Tuesday, February 28, 2012

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

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #447          www.energyefficientbuild.com
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
I have the following columns
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