Hi,
I am trying to come up with a formula that will count the number of non blank cells in each row and compare it to the number of non-blank cells in each column and decide if it should add them up. Let me give an example of a spreadsheet I am using and then try to explain it further.[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]
Let's say I have the table above. I want to add up the number of blank cells in each row but I only want to include the cells in the count if the number on non-blank cells in a column is less than or equal to 3. For example, for the first month, the number of non blank cells to count is 2. For the second month, the number of non blank cells is 4. For the third month, the number of non-blank cells is 6. However, for the fourth month, the number of non-blank cells is seven because the columns for person 1 and 2 have 4 rows worth of data.
In addition, I also want to have a way to use this same criteria to sum up the values in the corresponding cells. For example, for the first month, the total is 100. For the second month, the total would be 350. For the third month, the total would be 550. But for month 4, the total would also be 550 because the first 2 columns are excluded.