Hi,

Lets say I have the following table:

[TABLE="class: grid, width: 500"]

[tr]

[td]

Month

[/td]

[td]

Person 1

[/td]

[td]

Person 2

[/td]

[td]

Person 3

[/td]

[td]

Person 4

[/td]

[td]

Person 5

[/td]

[td]

Person 6

[/td]

[td]

Person 7

[/td]

[td]

Person 8

[/td]

[td]

Person 9

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

150

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td]

100

[/td]

[/tr]

[/TABLE]

I am trying to create a simple (if possible) formula that will add up the total for every month corresponding to each person if they meet the criteria that there is 3 months or less (think of them as cells) worth of data in those columns, including the current cell. Let me give some examples:

for Month 1, Persons 1 and 2 have data while the rest do not. They also meet the criteria of not having previous monthly data. Therefore, for Month 1, the total I want to sum up comes out to 200.

For Month 2, Persons 1 through 4 now have data but let's check and compare to the previous months. Persons 1 and 2 also have data in the previous month but the number of cells so far in columns 1 and 2 is only 2 so therefore they are still included in the summed total. Persons 3 and 4 fit the category described above for Month 1. So, the total for this month is 350

For Month 3, Persons 1 through 6 now have data but we have to check and compare to the previous months. For Persons 1 and 2, this is their 3rd month of data but that meets the criteria I set above. For Persons 3 and 4, they fit the category described for Month 2 seeing as they only have 2 months worth of data. Persons 5 and 6 meet the category for Month 1 since they only have 1 month worth of data. So the total for this month is 550.

For Month 4, There are now 9 people that have data but lets check and see which ones meet the criteria I stated above. Persons 1 and 2 now have data for 4 months (3 previous months and the current one) so they fail the criteria above and are therefor excluded. Persons 3 and 4 meet the category of Month 3 and are included. Persons 5 and 6 meet the category of Month 2 and are included. Person's 7, 8 and 9 meet the category of Month 1 and are included. So the total for this month is also 550 and not 700.

I understand that the criteria doesn't ever get broken until Month 4 so if necessary, a formula that works for Month 4 onward is fine but I would prefer a formula that would work for every month meaning that it would dynamically change to include more previous months as the number of the current month increases.