wildcards in sumproduct functions

  • Thread starter Thread starter K1
  • Start date Start date
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.
 
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
 
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.
 
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
 
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
 
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.
Click to expand...
Click to expand...
 
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.
 
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.
 
Thanks for your help. You guys are truly great and "spot on" with the
correct answers. I've learned a lot from you all.
 
Well, that won't help you specifically to look for text contained
"within" the cells, but post back if you need further help.

Pete
 
Back
Top