Count on multiple parameters

[TJ]

Hi
I have a spreadsheet which has 2 columns. Col A contains names and col b
contains either Open or Closed. I want to count the number of closed for
each name. E.g

Amy Open
Amy Closed
Amy Open
Steve Closed
Steve Closed

would give

Amy 1
Steve 2

I can count the names if a countif and the number of closed using countif -
I now want to CountIf(A1:A10,"=Amy") AND CountIf(B1:B10,"=Closed")
Any help please
TJ

 

[Alan]

=SUMPRODUCT(--(A1:A10="Amy"),--(B1:B10="Closed"))
You can also put 'Amy' and 'Closed' in two cells, say C1 and C2
=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))
Regards,
Alan.

 

[TJ]

Thanks Alan, I have tried what you suggest and it is still not working. The
function reads
=SUMPRODUCT(('MIP Raised'!$E$2:'MIP Raised'!$E$65336="Amy"),('MIP
Raised'!$B$2:'MIP Raised'!$B$65336="Closed"))
If the are 20 entires for Amy between E2 and E65336 and of those 5 are
closed in B2 to B65336 then this formula should show a result of 15. Mine
shows 0. Is there anything I am doing wrong?
TJ

 

[Guest]

Try:

=SUMPRODUCT(--('MIP Raised'!$E$2:$E$65336="Amy"),--('MIP
Raised'!$B$2:$B$65336="Closed"))

You missed out the -- which converts a true/False condition to 1/0 so you sum.