Sumproduct and Wildcards

  • Thread starter Thread starter ColleenK
  • Start date Start date
C

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.
 
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
 
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.)
 
Back
Top