count one column based on value in adjacent column

  • Thread starter Thread starter ash.gary
  • Start date Start date
A

ash.gary

Does anyone know how to count the number of cells in one column based
on a value in a second column? Example:

Column 1 Column 2
Car Green
Car
Car Green
Boat Green
Train Green

The empty cell was intentional. I would like to count the number of
"Green" in Column 2 only if it coincides with "Car" in Column 1, so
the result would = 2. I tried using Dcount but I couldn't get it to
work.

Thanks in advance for any help that you can provide.
 
Perhaps, try creating a third column and put in =IF(B1="green",1,0)
this will assign the value 1 if column 2 is green and 0 if it is not
green
then copy and paste this all the way down and total at the bottom.
 
Perhaps creating a third column and put into C1 the following:
=IF(AND(B1="green",A1="car"),1,0)
it will assign the value 1 if B1 is green and A1 is car
copy and past all the way to the bottom and total up at the bottom.
 
One way
In say, C1: =sumproduct((a1:a100="Car")*(b1:b100="Green"))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik










- Show quoted text -
Click to expand...

Thanks! It works perfectly.
 
Back
Top