Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Feb 28 '12 at 13:06

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

add comment

2 Answers 2

up vote 0 down vote accepted

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)

share|improve this answer
add comment

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)))
share|improve this answer
    
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
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 '12 at 13:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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