Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.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. |
#2
![]()
Posted to microsoft.public.excel.worksheet.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 - |
#3
![]()
Posted to microsoft.public.excel.worksheet.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 - |
#4
![]()
Posted to microsoft.public.excel.worksheet.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 - . |
#5
![]()
Posted to microsoft.public.excel.worksheet.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. . |
#6
![]()
Posted to microsoft.public.excel.worksheet.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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |