2

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 :(

I am using google-docs

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 ("")

Improve this question

2 Answers 2

Reset to default
0

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)

Improve this answer
3

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)))
Improve this answer
2
  • 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, 2012 at 12:24
  • 1
    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, 2012 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.