multiple criteria with a hitch

jberwald

Board Regular
Joined
May 3, 2005
Messages
205
I need excel to calculate how many times this occurs:

That column C contains an X
when column H contains a certain name.

My problem is that column H can contain two names at times (ie John/Harry) and I would want this cell to be counted in both a John calculation and a Harry calculation.

I have tried this, but it only works on cells containing a single name

=SUMPRODUCT(--($C$3:$C211="X"),--($H3:$H211="John"))

Thanks in advance.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
a condition you can use for that is:

Code:
LEN(SUBSITUTE($H3:$H211,"John",""))<LEN($H3:$H211)

Rather than

$H3:$H211="John"

That way if "John" appears anywhere in the cell, it'll count it.

Edited to fix formula not showing up correctly.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

try this
   C  D  E    F     G  H                      
 1       John Harry                           
 2       3    2                               
 3 x                   john                   
 4                     name                   
 5 x                   where does John live ? 
 6 x                   nothing here           
 7 x                   Garry                  
 8                     Harry                  
 9 x                                          
10 x                   Harry & John           
11 x                                          
12 x                   Harry                  

Blad1

[Table-It] version 06 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
E2:F2   =SUMPRODUCT(--($C$3:$C$211="X"),--(ISNUMBER(SEARCH(E1,$H$3:$H$211))))

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,150
Members
410,543
Latest member
ExcelGlenn
Top