wildcards in sumproduct functions

K1

unread,

Feb 17, 2010, 7:38:04 PM2/17/10

to

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.

Pete_UK

unread,

Feb 17, 2010, 7:52:24 PM2/17/10

to

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. Valko

unread,

Feb 17, 2010, 7:55:45 PM2/17/10

to

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.

--
Biff
Microsoft Excel MVP

"K1" <K...@discussions.microsoft.com> wrote in message
news:4B03937C-A861-4DC8...@microsoft.com...

ker_01

unread,

Feb 17, 2010, 7:58:01 PM2/17/10

to

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

Pete_UK

unread,

Feb 17, 2010, 8:12:33 PM2/17/10

to

Hi Biff,

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

Spooky !! <bg>

Pete

> > adding equations. I have tried versions of "*ABC*" and get false values.- Hide quoted text -
>
> - Show quoted text -

T. Valko

unread,

Feb 17, 2010, 8:22:48 PM2/17/10

to

>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

"Pete_UK" <pash...@auditel.net> wrote in message
news:171a4295-e69b-4ab9...@v25g2000yqk.googlegroups.com...

K1

unread,

Feb 17, 2010, 11:18:05 PM2/17/10

to

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

K1

unread,

Feb 17, 2010, 11:20:01 PM2/17/10

to

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.

"Pete_UK" wrote:

> .
>

K1

unread,

Feb 17, 2010, 11:28:01 PM2/17/10

to

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.

"T. Valko" wrote:

> .
>

K1

unread,

Feb 17, 2010, 11:32:01 PM2/17/10

to

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

> .
>

T. Valko

unread,

Feb 17, 2010, 11:49:22 PM2/17/10

to

You're welcome. Good luck!

--
Biff
Microsoft Excel MVP

"K1" <K...@discussions.microsoft.com> wrote in message

news:6E0A89D5-7F7B-4337...@microsoft.com...

Pete_UK

unread,

Feb 18, 2010, 4:33:15 AM2/18/10

to

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

Pete

shahni...@gmail.com

unread,

Jul 11, 2017, 8:28:52 AM7/11/17

to

This is very useful formula thank you..