COUNTIFS function not recognizing criteria for "if date in P is greater than date in D"

Hello,

I'm using the countifs function to compile some entries. I was using the string: =COUNTIFS(E:E,"Word*",P:P,"") in order to return the number of rows containing Initial* in E and no dates in P.

What I actually need is the same for the partial match, but the second criteria would need to be if cell in P is either blank or if the date is smaller than date in column D.

I tried: =COUNTIFS(E:E,"Word*",P:P,"<D:D") but it does not work.

I managed to get a count of the all the rows in which the date in P is smaller than the date in D with SUMPRODUCT, with: =SUMPRODUCT(--(D:D>P:P))

However, I need to have a count of the rows with "(partial Word match in E) AND (P=blank OR P<D)"

What I don't understand is how come I get the same result with =COUNTIFS(E:E,"Word*",P:P,"<D:D") than what I get with =COUNTIFS(E:E,"Word*",P:P,"")

Thanks again for all the great help.

=COUNTIFS(E:E,"Word*",P:P,"<"&D:D)

countifs is expecting the criteria to be a single value, not an array.

You could put in a helper column (they're not a bad thing)
Say in Q2 and filled down
=P2<D2

Then use
=COUNTIFS(E:E,"Word*",Q:Q,TRUE)

countifs is expecting the criteria to be a single value, not an array.

Jonmo1
here - yes,
but not always
=SUM(COUNTIFS(A1:A12,B1:B12)) ,CSE

I just got around to testing this. This formula seems to work without any helper columns:

=SUMPRODUCT((ISNUMBER(SEARCH("Word",E:E)))*(P:P<D:D))

Just change the whole column references to something smaller.

Or try:

=SUMPRODUCT(ISNUMBER(SEARCH("Word",E1:E10))*((P1:P10="")+(P1:P10<D1:D10)>0))

Originally Posted by tim201110

=SUM(COUNTIFS(A1:A12,B1:B12)) ,CSE

COUNTIFS is still really only seeing 1 cell at a time, B1 then B2 then B3
It's basically doing
COUNTIFS(A1:A12,B1)+COUNTIFS(A1:A12,B2)+COUNTIFS(A1:A12,B3)+etc.