CODEWITHSUNDEEP
excelexcel-2003
George
George

Reputation: 4674

How to nest countif function in Excel? (AND BLANK)

I have an excel sheet that has 2 column, Start (A) and End (B). I would like to count how many occurance of a certain date and no End date. How can I nest the count(if...) function?

This is what I have so far: =COUNTIF(Sheet1!A2:A999, "4/13") but I don't know how to do AND COLUMN B == BLANK. Thank you.

Start   End
4/13    4/13
4/13    5/13
4/13    
4/13    
4/13    
4/13    
4/13    
4/13    
4/13    5/13
4/13    4/13
4/13    4/13
4/13    
4/13    4/13
4/13    4/13
4/13    5/13
4/13    4/13

Upvotes: 0

Views: 934

Answers (2)

tigeravatar
tigeravatar

Reputation: 26670

Excel 2007 and higher:

=COUNTIFS(A2:A17,"4/13",B2:B17,"")

Excel 2003 and lower:

=SUMPRODUCT(--(A2:A17=--"4/13/2014"),--(B2:B17=""))

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Use COUNTIFS

=COUNTIF(Sheet1!A2:A999, "4/13",Sheet1!B2:B999,"")

or SUMPRODUCT

=SUMPRODUCT((Sheet1!A2:A999="4/13")*(Sheet1!B2:B999=""))

Upvotes: 0

Related Questions