Sumproduct and Wildcards
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.)
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"ColleenK" wrote:
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.
--
CK
|