How to use OR in a COUNTIF function when dealing with dates and ">" characters

I want to write a function whereby I could count a cell if it is blank OR greater than a date.

This is a piece in what will be a much larger formula so looking to avoid a solution of SUM(countif(D:D,""),countif(D:D,">"&E1))

The function I have currently looks like this:

Sum((Countifs(D:D,{">"&E1,""}))

Where E1 = 12/1/2015

Thanks for your help!

Joel A Silverberg

Posted 2016-02-02T18:54:59.797

Reputation: 11

Answers

COUNTIFS() doesn't seem to like mixing array constants and cell references like that. It works fine if you hard-code the date like this:

=SUM(COUNTIF(D1:D4,{">12/1/2015",""}))

Or if you don't want to hard-code it, use this array formula. This formula must be confirmed with ctrl+shift+enter.

=SUM(--(D1:D4>E1),--(D1:D4=""))

Note that both of these solutions, as well as just =COUNTIF(D:D,"") will count every blank cell in the column, so you can't select the entire column (unless your data fills the entire column).

Kyle

Posted 2016-02-02T18:54:59.797

Reputation: 2 286

It isn't that COUNTIFS doesn't like the mixing of array constants and cell references. It is that an array constant cannot contain a cell reference. Array constants can contain only numbers, text, logical values (such as TRUE and FALSE), and error values ( such as #N/A). – Ron Rosenfeld – 2016-02-04T03:06:12.190

You can construct the required array with a little manipulation, e.g.:

=SUMPRODUCT(COUNTIF(D:D,IF({0,1},">"&E1,"")))

though are you sure you really want to count all blanks within the entirety of column D?

Regards

XOR LX

Posted 2016-02-02T18:54:59.797

Reputation: 1 097