Evaluating mulitple criteria with COUNTIF

[JRJ]

Can anyone help me with a formula to count the number of occurrences a color
appears for a particular location?
Sample Data:

Location Color
2242 ALMOND
2247 ALMOND
2247 WHITE
2247 WHITE
2247 BLACK
2242 BLACK
2242 WHITE
2242 ALMOND
2242 BLACK
2242 ALMOND

Example Result:

Location ALMOND WHITE BLACK
2242 3 1 2
2247 1 2 1

Thanks.

 

[Don Guillett]

=sumproduct(--a2:a22=2242),--(b2:b22="almond")
or
=sumproduct((a2:a22=2242)*(b2:b22="almond"))

 

[Tom Hutchins]

You can do this easily with a pivot table. Put Location as a Row field, Color
as a Column field, and Color as the Data field. By default, it will count the
number of occurences.

You could alternatively use SUMPRODUCT formulas like this:
=SUMPRODUCT(--(A2:A11="2242"),--(B2:B11="Almond"))

Hope this helps,

Hutch

 

[Ron Coderre]

Would you consider using a Pivot Table?

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW: Drag the LOCATION field here
COLUMN: Drag the COLOR field here
DATA: Drag the COLOR field here, too.
(It will list as Count of COLOR)
Click [OK]
Select where you want the Pivot Table.Click [Finish].

That will list LOCATION down the left
COLOR across the top and
the count for each LOCATION/COLOR combination

To refresh the Pivot Table, just right click it and select Refresh Data

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm
http://www.contextures.com/tiptech.html

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[JRJ]

You guys gave me a couple of different options that all work.
Thanks!

Would you consider using a Pivot Table?

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW: Drag the LOCATION field here
COLUMN: Drag the COLOR field here
DATA: Drag the COLOR field here, too.
(It will list as Count of COLOR)
Click [OK]
Select where you want the Pivot Table.Click [Finish].

That will list LOCATION down the left
COLOR across the top and
the count for each LOCATION/COLOR combination

To refresh the Pivot Table, just right click it and select Refresh Data

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm
http://www.contextures.com/tiptech.html

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Can anyone help me with a formula to count the number of occurrences a
color
appears for a particular location?
Sample Data:

Location Color
2242 ALMOND
2247 ALMOND
2247 WHITE
2247 WHITE
2247 BLACK
2242 BLACK
2242 WHITE
2242 ALMOND
2242 BLACK
2242 ALMOND

Example Result:

Location ALMOND WHITE BLACK
2242 3 1 2
2247 1 2 1

Thanks.