COUNTIFS with greater than TODAY OR blank cell

user_5410380 0
Viewed 2855

I have a question regarding COUNTIFS. The simple boiled down version of what I am trying to do is this:

I am trying to use COUNTIFS to count the number of entries that the cell in a column is either blank or in the future (greater than today) and that is marked with an “X” in another column.

There are several other renditions in the formula but if I can get this, I can get the rest. So far, I have this:

=SUM(COUNTIFS($C$2:$C$50,{"";">"&TODAY()},$E$2:$E$50,"X"))

Excel won’t let me return out of the formula and highlights the quotation mark following the greater than symbol.

=SUM(COUNTIFS($C$2:$C$50,{"";">100"},$E$2:$E$50,"X")) works fine when I play around and test things but when I try to add in &TODAY() or reference a cell, things go sideways.

Any suggestions as to what I am doing wrong? The actual formula is quite long and there are several comparisons that are made between columns. I've seen some references to using SUMPRODUCT but haven't been able to figure it out that way either.

excel-formula excel
1 Answers

You can use a formula to generate the criteria array, i.e.

=SUMPRODUCT(COUNTIFS($C$2:$C$50,IF({1;0},"",">"&TODAY()),$E$2:$E$50,"X"))

I used SUMPRODUCT in this version because with SUM you'd need CTRL+SHIFT+ENTER

The IF function generates an array that resolves to something like this:

{"";">43060"}

user_11242870
Related