Countif works, Sumproduct doesn't

[Guest]

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

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

=SUMPRODUCT((E1:E1000<EDATE(TODAY(),-6))*(D11000="*"&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. Valko]

Sumproduct won't work with wildcards in that manner.

Try it this way:

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

Biff

 

[Bob Phillips]

Because SP doesn't support wildcards

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



--
---
HTH

Bob

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

 

[Guest]

Thanks. That works.

Sumproduct won't work with wildcards in that manner.

Try it this way:

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

Biff

 

[T. Valko]

You're welcome!

Biff

Thanks. That works.