COUNTIF cell A1 is x and cell B1 is y

G

Guest

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?
 
B

Bob Phillips

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

--

HTH

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

Peo Sjoblom

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif Function Using "AND" 3
how do i sum if in two different cell references - Excel 1
want to count in two columns; countif (colA=x AND colB=y)? 1
Conditional formatting Max to a entire row A1:D1 with E1 being Ma 2
How to populate a cell based on font color/threshold 1
IF cell A1 contains "X", enter "X" in cell B1, if not enter "Y" HE 2
find all instances of a search item and put result in a single cel 5
how do i count how many times x in column a = 1 or 2 in column b 8

Top