COUNTIF with 2 conditions

  • Thread starter Thread starter eddymontreal
  • Start date Start date
E

eddymontreal

I have a worksheet with 2 columns:
- The first column has different names (e.g. John Smith, Laura Le)
- The second column has Y (for yes) and N (for no) values

I'm using the following function to count how many times I have "Joh
Smith" in column 1:
=COUNTIF('Sheet1'!A1:A1000,"John Smith")

What would be the formula if I want to count how many time John Smit
has Y in column 2? In other words, I want to sort column 1 by name
and count how many times I have Y for each name.

Thank you

E
 
Eddy,

Untested, but this should work.

=SUMPRODUCT((A1:A1000="John Smith")*(B1:B1000="Y"))

=SUMPRODUCT((A1:A1000=C1)*(B1:B1000=C2))
Where C1 and C2 hold names and letters.

Dan E
 
Eddy,

the syntax is

SUM((A1:A10="GOOD")*(B1:B10>0))

this counts the number of rows that have "GOOD" in column A and which
value is superior to 0 in column B.

if you want to do a SUMIF with 2 conditions, it's

SUM((A1:A10="GOOD")*(B1:B10>0)*(C1:C10))

same as above but sums values in column C instead of counting
occurences.
 
Sorry, to use your example that would be:

SUM(('Sheet1'!A1:A1000="John Smith")*('Sheet1'!B1:B1000="Y"))
 
GREAT! Thank you all for your help. Now I have a couple of formulas to
work with.

Ed
 
u probably know this, but entering those with ctrl shift enter would produce
the same result.
 
Back
Top