Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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




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




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




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




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  
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Sumproduct and wildcards  Excel Worksheet Functions  
Wildcards in functions  Excel Worksheet Functions  
Wildcards in Functions  Excel Worksheet Functions  
Wildcards in Functions  Excel Worksheet Functions  
Wildcards with SumProduct  Excel Worksheet Functions 