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?

 

[John C. Harris, MPA]

you can't put it in the same cell. You need to put the same formula with the
CounIf parameters changed in another cell for the other parameters.

 

[RagDyeR]

Say you enter the home code you're looking for in E1,
And the color in E2,

Then try this:

=SUMPRODUCT((C3:C20=E1)*(F3:F20=E2))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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?

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(C3:C20="CL2"),--(F3:F20="BURG"))

(See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of
the "--")

If you're interested in knowing the counts of all the combinations, you
can't beat a pivot table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm