Count on multiple parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TJ - 27 Feb 2007 18:57 GMT

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

Reply to this Message

Alan - 27 Feb 2007 19:11 GMT

=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.

> Hi
> I have a spreadsheet which has 2 columns. Col A contains names and col b
[quoted text clipped - 17 lines]
> Any help please
> TJ

Reply to this Message

TJ - 27 Feb 2007 19:45 GMT

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

> =SUMPRODUCT(--(A1:A10="Amy"),--(B1:B10="Closed"))
> You can also put 'Amy' and 'Closed' in two cells, say C1 and C2
[quoted text clipped - 22 lines]
>> Any help please
>> TJ

Reply to this Message

Toppers - 27 Feb 2007 20:23 GMT

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.

> Thanks Alan, I have tried what you suggest and it is still not working. The
> function reads
[quoted text clipped - 31 lines]
> >> Any help please
> >> TJ

Reply to this Message