# How to count cells that are below a certain date and check if other cells in the current row are empty?

I need to count how many cells in a specific column are below a certain date. Also, at the same time, the counter should only be increased, if some other cells next to each cell being counted are empty.

Example

Columns:

``````Date1          Date2            Date3
01/02/2012     01/02/2012       01/02/2012
01/02/2012     01/02/2012       01/02/2012
01/02/2012     01/02/2012       01/02/2012
01/02/2012
01/02/2012
01/02/2012     01/02/2012       01/02/2012
``````

I need to count how many cells in the column Date2 are below some date AND where date1 + date3 are empty. The result would be: 2

Currently, i only know how to count the number of cells below a certain date:

``````=COUNTIF(ArrayFormula(Mona!\$F\$4:\$F\$400); "<="&\$AY12)
``````

But this doesnt work with the other criteria :(

REAL DATA

Columns:

``````B         C          D            E
``````

I need to count the number of cells where C is below AY12, and where B, D and E are empty ("")

I think the question asks for a count of rows where the date is before AY12 and the other columns are blank (not populated). It's simpler to "nest" the IF statements, I think - I'd use this version

`=ARRAYFORMULA(SUM(if(Mona!\$C\$4:\$C\$400<=\$AY12,if(Mona!\$B\$4:\$B\$400="",if(Mona!\$D\$4:\$D\$400="",if(Mona!\$E\$4:\$E\$400="",1))))))`

Note that blank cells in column C will also be deemed to be <=AY12 so if those might be blank too then you might want to add a check to ensure that that column is not blank, i.e.

`=ARRAYFORMULA(SUM(if(Mona!\$C\$4:\$C\$400<=\$AY12,if(Mona!\$C\$4:\$C\$400<>"",if(Mona!\$B\$4:\$B\$400="",if(Mona!\$D\$4:\$D\$400="",if(Mona!\$E\$4:\$E\$400="",1)))))))`

Replace commas with semi-colons if your version requires it

Note that your COUNTIF formula quoted above doesn't need the "Arrayformula" part, can simply be this

`=COUNTIF(Mona!\$F\$4:\$F\$400;"<="&\$AY12)`

In Excel you'd use `=countifs()`, rather than `=countif()`

You could emulate it like this:

``````=ARRAYFORMULA(SUM(if(if(Mona!\$F\$4:\$F\$400<=\$AY12,1,0)+if(Mona!\$E\$4:\$E\$400<>"",1,0)+if(Mona!\$D\$4:\$D\$400<>"",1,0)=3,1,0)))
``````
• Thanks for your reply. What does the ,1,0 and =3,1,0 do? I will edit my question with the real data, would you perhaps be so kind as to edit your answer according to this? – Kenci Feb 28 '12 at 12:24
• Jon is using 3 inner if statements. In the first case he is checking to see if it is < AY2 if it is it as given value of 1. He also checks two different columns for blank values. If a cell is blank it is given a value of 1. He then adds those 3 values together. If he gets a result of 3, he sets the final value to 1. if he doesn't have a value of 3, he sets the final value to 0. – mhoran_psprep Feb 28 '12 at 13:36