|
Evaluating mulitple criteria with COUNTIF
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. |
Evaluating mulitple criteria with COUNTIF
=sumproduct(--a2:a22=2242),--(b2:b22="almond")
or =sumproduct((a2:a22=2242)*(b2:b22="almond")) -- Don Guillett Microsoft MVP Excel SalesAid Software "JRJ" wrote in message ... 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. |
Evaluating mulitple criteria with COUNTIF
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 "JRJ" wrote: 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. |
Evaluating mulitple criteria with COUNTIF
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/datam...ablereport.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" wrote in message ... 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. |
Evaluating mulitple criteria with COUNTIF
You guys gave me a couple of different options that all work.
Thanks! "Ron Coderre" wrote: 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/datam...ablereport.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" wrote in message ... 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. |
| All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com