multiple criteria with a hitch

[jberwald]

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.

 

[yee388]

a condition you can use for that is:

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]

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

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