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!

2 Answers 2

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

1
  • 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). Feb 4, 2016 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .