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.