Ask Your Question
0

Count rows based on three criteria, including blank cell [closed]

asked 2013-12-08 15:36:22 +0200

Kees gravatar image

I have three columns, the first two contain a date, the third one a number or a blank cell. I am trying to count all the rows in those columns where a date (in D3) falls between the dates in the first two columns and the third column contains an empty cell.

At the moment my formula looks like this:

=COUNTIFS(A2:A73,"<="&D3,B2:B73,">="&D3,C2:C73,"")

I have also tried:

=COUNTIFS(A2:A73,"<="&D3,B2:B73,">="&D3,C2:C73,ISBLANK(this.cell())

The first two critera seem to work, but as soon as I add the third one (check for empty cell) the result is always 0.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-16 14:23:34.658661

1 Answer

Sort by » oldest newest most voted
0

answered 2013-12-08 16:34:34 +0200

m.a.riosv gravatar image

I think no option for use a function inside COUNTIFS() function, but it easy to do with SUMPRODUCT():

=SUMPRODUCT(A2:A73<=D3;B2:B73>=D3;ISBLANK(C2:C73))

edit flag offensive delete link more

Comments

Thanks that really helped. It works, though it seems a little unintuitive, I would never have worked out by myself to use this function for what I was trying to do.

Kees gravatar imageKees ( 2013-12-19 15:31:09 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-12-08 15:36:22 +0200

Seen: 1,310 times

Last updated: Dec 08 '13