COUNTIF cell A1 is x and cell B1 is y
 

I have two columns - one lists the city; the other lists other criteria; like
colours (green/red, etc).
I want to be able to count the number of 'Red' values in the second column
only if the value in the first column is, say, "Toronto".

So, countif (A1:A1500,"Toronto" AND IF B1:B1500, "Red")
The result might be that there are 500 occurences of "Toronto" but of those,
only 240 are associated with "Red" in the second column. Can you help?

=SUMPRODUCT(--(A1:A1000="Toronto"),--(B1:B1000="red"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Heather Murch" <Heather wrote in message
...
I have two columns - one lists the city; the other lists other criteria;
like
colours (green/red, etc).
I want to be able to count the number of 'Red' values in the second column
only if the value in the first column is, say, "Toronto".

So, countif (A1:A1500,"Toronto" AND IF B1:B1500, "Red")
The result might be that there are 500 occurences of "Toronto" but of
those,
only 240 are associated with "Red" in the second column. Can you help?

One way

=SUMPRODUCT(--(A1:A1500="Toronto"),--(B1:B500="Red"))

replace the hardcoded criteria with cells like this

=SUMPRODUCT(--(A1:A1500=C1),--(B1:B500=D1))

that way you only need to change the contents of C1 and D1 instead
of editing the formula when you change city and colour


--

Regards,

Peo Sjoblom


"Heather Murch" <Heather wrote in message
...
I have two columns - one lists the city; the other lists other criteria;
like
colours (green/red, etc).
I want to be able to count the number of 'Red' values in the second column
only if the value in the first column is, say, "Toronto".

So, countif (A1:A1500,"Toronto" AND IF B1:B1500, "Red")
The result might be that there are 500 occurences of "Toronto" but of
those,
only 240 are associated with "Red" in the second column. Can you help?