Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count cells containing text, multiple criteria
I have names in A2:A5 (Joe, Jim, Jake Jones, Jeff Johnson) and birthdates in B2:B5(1980, 1980, 1980, 1981). In A1 is the name criteria (eg "Jake"), and in A2 is the birthdate criteria (eg "1980").
I would like to count the number of times a row satisfies both the name and birthdate criteria. In the case of looking for "Jake" and 1980, it would satisfy Jake Jones, 1980 which is in A4 and B4, and therefore return 1. I'm attempting the formula: =SUMPRODUCT((A2:10="*"&A1&"*")*(B2:K2=B1)) but the trouble is, I think, trying to use the wildcards in an array function, which apparently is no go. Is there a better way to do this? Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count cells containing text, multiple criteria
Hi,
Am Tue, 26 Aug 2014 17:59:51 -0700 (PDT) schrieb : I'm attempting the formula: =SUMPRODUCT((A2:10="*"&A1&"*")*(B2:K2=B1)) in a SUMPRODUCT formula the ranges must have the same dimension: =SUMPRODUCT(--(A2:A10=A1),--(B2:B10=B1)) Try it with COUNTIFS: =COUNTIFS(A2:A10,"*"&A1&"*",B2:B10,B1) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count cells containing text, multiple criteria
Awesome, I did not know about COUNTIFS!! Thanks for the help!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurance of multiple text criteria | Excel Discussion (Misc queries) | |||
count using multiple criteria including text strings | Excel Worksheet Functions | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
count cells using multiple criteria | Excel Worksheet Functions | |||
count cells using multiple criteria | Excel Discussion (Misc queries) |