Sumproduct and Wildcards
 

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

Sumproduct and Wildcards
 

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"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

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

Sumproduct and Wildcards
 

Thank you so much, this worked beautifully.
--
CK


"Luke M" wrote:

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

Sumproduct and Wildcards
 

Thank you so much, this worked beautifully.
--
CK


"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"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