Sumproduct and Wildcards

[ColleenK]

Can you use wildcards and a text string with the sumproduct statement, for
example I would like to find the word "accural" in a string and base the
sumproduct on that, here is my example;

=SUMPRODUCT(--(Transactions!$C$1:$C$65000="*Accural*"),--(Transactions!$C$1:$C$65000<>""),(Transactions!$L$1:$L$65000))

This example does not work.

 

[Jacob Skaria]

Try

=SUMPRODUCT(--(ISERROR(SEARCH("*Accural*",Transactions!$C$1:$C$65000))=FALSE),--(Transactions!$C$1:$C$65000<>""),Transactions!$L$1:$L$65000)

If this post helps click Yes

 

[Luke M]

To use wildcards, need to use the SEARCH/FIND function.

=SUMPRODUCT(--(ISNUMBER(SEARCH("*Accural*",Transactions!$C$1:$C$65000))),--(Transactions!$C$1:$C$65000<>""),(Transactions!$L$1:$L$65000))

If you need this to be case-sensitive, change SEARCH to FIND.
(Note that in this case, the wildcards aren't really necessary, as
SEARCH/FIND look in entire string. However, if you wanted to look for
something with a wildcard in middle, it would be more useful.)

 

[ColleenK]

Thank you so much, this worked beautifully.

 

[ColleenK]

Thank you so much, this worked beautifully.