Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
k1 k1 is offline
external usenet poster
 
Posts: 7
Default wildcards in sumproduct functions

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default wildcards in sumproduct functions

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

On Feb 18, 12:38*am, K1 wrote:
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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default wildcards in sumproduct functions

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" wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default wildcards in sumproduct functions

Hi Biff,

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

Spooky !! <bg

Pete

On Feb 18, 12:55*am, "T. Valko" wrote:
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" wrote in message

...



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.- Hide quoted text -


- Show quoted text -


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default wildcards in sumproduct functions

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" wrote in message
...
Hi Biff,

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

Spooky !! <bg

Pete

On Feb 18, 12:55 am, "T. Valko" wrote:
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" wrote in message

...



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.- Hide quoted text -


- Show quoted text -





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
k1 k1 is offline
external usenet poster
 
Posts: 7
Default wildcards in sumproduct functions

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:

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

On Feb 18, 12:38 am, K1 wrote:
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.


.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
k1 k1 is offline
external usenet poster
 
Posts: 7
Default wildcards in sumproduct functions

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:

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" wrote in message
...
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.



.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
k1 k1 is offline
external usenet poster
 
Posts: 7
Default wildcards in sumproduct functions

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:

Hi Biff,

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

Spooky !! <bg

Pete

On Feb 18, 12:55 am, "T. Valko" wrote:
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" wrote in message

...



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.- Hide quoted text -


- Show quoted text -


.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default wildcards in sumproduct functions

You're welcome. Good luck!

--
Biff
Microsoft Excel MVP


"K1" wrote in message
...
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:

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" wrote in message
...
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.



.



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default wildcards in sumproduct functions

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

Pete

On Feb 18, 4:20*am, K1 wrote:
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:
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


On Feb 18, 12:38 am, K1 wrote:
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.


.- Hide quoted text -


- Show quoted text -




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default wildcards in sumproduct functions

On Thursday, February 18, 2010 at 6:22:24 AM UTC+5:30, Pete_UK wrote:
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

On Feb 18, 12:38Â*am, K1 wrote:
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.


This is very useful formula thank you..
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct and wildcards vipa2000 Excel Worksheet Functions 18 July 31st 05 09:24 PM
Wildcards in functions oops Excel Worksheet Functions 4 July 19th 05 11:12 PM
Wildcards in Functions Graeme Crook Excel Worksheet Functions 1 June 10th 05 10:42 PM
Wildcards in Functions Graeme Crook Excel Worksheet Functions 1 June 9th 05 11:31 PM
Wildcards with SumProduct pomalley Excel Worksheet Functions 7 March 24th 05 04:01 PM


All times are GMT +1. The time now is 09:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"