Countif Help in Excel

[[email protected]]

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.

--
John C. Harris, MPA
JCZ Consulting Services, LLC
<[email protected]> wrote in message
news:[email protected]...
>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?
>

[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!
=====================================================

<[email protected]> wrote in message
news:[email protected]...
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



In article <[email protected]>,
[email protected] wrote:

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