Countif works, Sumproduct doesn't

J

Jennifer

I have two functions here. The sumproduct function is just adding one more
set of criteria to look at when counting.

=COUNTIF(D:D,"*"&A1&"*")

=SUMPRODUCT((E1:E1000<EDATE(TODAY(),-6))*(D1:D1000="*"&A1&"*"))

Why does the Countif function work, but when I used it in a Sumproduct
function, it gives me zero? I know there should me more than zero.

Am I doing something wrong here?

Thanks,

Jennifer
 
T

T. Valko

Sumproduct won't work with wildcards in that manner.

Try it this way:

=SUMPRODUCT((E1:E1000<EDATE(TODAY(),-6))*(ISNUMBER(SEARCH(A1,D1:D1000))))

Biff
 
B

Bob Phillips

Because SP doesn't support wildcards

=SUMPRODUCT((E1:E1000<EDATE(TODAY(),-6))*(ISNUMBER(FIND(A1,D1:D1000))))



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

Jennifer

Thanks. That works.

T. Valko said:
Sumproduct won't work with wildcards in that manner.

Try it this way:

=SUMPRODUCT((E1:E1000<EDATE(TODAY(),-6))*(ISNUMBER(SEARCH(A1,D1:D1000))))

Biff
Click to expand...
 
Top