View Single Post
  #3   Report Post  
Old February 18th 10, 12:55 AM posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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.