Countif Help in Excel

[pkruti]

I need help creating a formula in MS Excel.


All the data in column "F" (F3 to F20) is recorded as a color for
example "BURG", "COFFEE", "WHITE", etc. And column "C" (C3 to C20) is
recorded as "CL2", "OP3", "THB", etc which are code names for homes.

I need a formula to know how many times "CL2" selected the color
"BURG". I believe to do this you have to use a COUNTIF formula but i
just cant figure the formula out. I can count to see how many BURG
there are but i cant do both.

The formula i started off using is =COUNTIF(F3:F20, "BURG") which gives
me the correct count of BURG but how do i add the second criteria?

Any ideas?

 

[David Benson]

If it were me, I would use an array formula rather than COUNTIF.

={sum(if("CL2"=C3:C20,if("BURG"=F3:F20,1,0),0))}

Note that when you enter the formula, you DO NOT type the curly brackets
("{" and "}"). They will appear when you enter the formula as an array
formula -- which you do by pressing <CTRL><ALT><ENTER> (press Enter while
holding down both Ctrl and Alt).

Good luck,

-- David