Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Wildcards | Excel Worksheet Functions | |||
Wildcards in SUMPRODUCT | Excel Worksheet Functions | |||
Wildcards with Sumproduct? | Excel Discussion (Misc queries) | |||
Sumproduct with wildcards | Excel Worksheet Functions | |||
Sumproduct and wildcards | Excel Worksheet Functions |