1

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!

1

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).

  • 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 Feb 4 '16 at 3:06
0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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