wildcards in sumproduct functions

K

K1

I would prefer to use a wildcard for text criteria in sumproduct counting and
adding equations. I have tried versions of "*ABC*" and get false values.
 
P

Pete_UK

You would need to use something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("ABC",A1:A10))), other conditions...)

i.e. you are looking to see if ABC is contained within any of the
cells in the range A1 to A10. You can use FIND instead of SEARCH if
the case of the text string is important.

Hope this helps.

Pete
 
T

T. Valko

SUMPRODUCT doesn't accept wildcards.

Try something like this to count cells in A1:A10 that might contain "ABC"
anywhere within the cell:

=SUMPRODUCT(--(ISNUMBER(SEARCH("ABC",A1:A10))))

Note that case is not factor. ABC is the same as abc.

If in your application case *is* a factor then replace SEARCH with FIND.

Using FIND, ABC is not the same as abc.
 
K

ker_01

For straight counting, this might work (you could probably also adapt this
into a sumproduct). The key is that Search allows you to use a wildcard (FIND
does not), and search will return an error when the string isn't found, so
you have to put in a condition to catch the errors.

Note that this example has to be entered as an array formula
[Cntrl-Shift-Enter]

=SUM(IF(ISERROR(SEARCH("xyz",A1:A10)>0),0,IF(SEARCH("xyz",A1:A10)>0,1,0)))

HTH,
Keith
 
T

T. Valko

there are times when our answers are so similar ....

You really know your stuff! <bg>

Well, you know how I fell about that.

Cheers!

--
Biff
Microsoft Excel MVP


Hi Biff,

there are times when our answers are so similar ....

Spooky !! <bg>

Pete
 
K

K1

Thanks for your help but I think I have my answer... sumproduct does not
support wildcards. I use a specific text reference instead.

ker_01 said:
For straight counting, this might work (you could probably also adapt this
into a sumproduct). The key is that Search allows you to use a wildcard (FIND
does not), and search will return an error when the string isn't found, so
you have to put in a condition to catch the errors.

Note that this example has to be entered as an array formula
[Cntrl-Shift-Enter]

=SUM(IF(ISERROR(SEARCH("xyz",A1:A10)>0),0,IF(SEARCH("xyz",A1:A10)>0,1,0)))

HTH,
Keith

K1 said:
I would prefer to use a wildcard for text criteria in sumproduct counting and
adding equations. I have tried versions of "*ABC*" and get false values.
 
K

K1

Thanks for your help but I think I have my answer... sumproduct does not
support wildcards. I'll use a specific text reference or cell reference
instead.
 
K

K1

Thanks for supplying the ultimate response. Disappointly, sumproduct does
not have some of the capabilities that its simple sister, sumif does. I'll
use a cell reference as my search criteria instead of text.
 
K

K1

Thanks for your help. You guys are truly great and "spot on" with the
correct answers. I've learned a lot from you all.
 
P

Pete_UK

Well, that won't help you specifically to look for text contained
"within" the cells, but post back if you need further help.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top