# How to count how many cells in a column 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 ("")

-

## migrated from stackoverflow.comFeb 28 '12 at 13:06

This question came from our site for professional and enthusiast programmers.

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()`
``````=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)))