## Tuesday, February 28, 2012

### yogi_Count Number of Cells That Meet A Creiterion And Cells In Corresponding Rows Of Columns Are Blank

user nooff said:
How to count number of cells in a column, that are below a specific date, AND that neighbouring cells are not blank
Hi
I have the following columns
B C D E
I need to count the number of cells in column C, where C is less than or equal to a specific date, AND cells B D and E should be empty at the same time. Also, the cell C should not be empty, because if it is, than it will automatically be less than or equal to the date. This is what i have come up with:
=SUM(if(if(\$C\$12:\$C\$400<=\$AY12;1;0)+if(ISBLANK(\$B\$12:\$B\$400); 1; 0)+if(ISBLANK(\$D\$12:\$D\$400);1;0)+if(ISBLANK(\$E\$12:\$E\$400);1;0)++if(NOT(ISBLANK(\$C\$12:\$C\$400));1;0)=5;1;0))
So, i check:
If C is less than or equal to the date = +1
If B is blank = +1
If D is blank +1 If E is blank +1 AND,
if C is not blank: +1
The total should be: 5, and it should sum up it all up. I have a date column, and i drag this function downards the 30'th of June. The date changes for each row. There is only 1 row that meets this criterium, where the date is 15/02/2012 When i get to this date, it shows 1, but when i get to the 16 and after, it shows 0 again, where it should be showing 1.
What am i doing wrong?
-------------------------------------------------
following is a solution based on my best understanding of what user nooff is trying to do ... I have use a smaller range of B12:D24, and I have used the date for comparison in cell A12