How to count multiple values

Microsoft Excel 2003 (full product)
September 6, 2009 at 23:23:56
Specs: Windows XP
Can someone show me how to count the following value? I have a table with 2 fields (Product and Type). I like to count the number of product by type (ie. AAA will have 3 Large and 2 Medium, BBB will have 1 Large, 1 Medium and 2 Small.

Many thanks.


Product Type
AAA Large
AAA Large
AAA Large
AAA Medium
AAA Medium
BBB Large
BBB Medium
BBB Small
BBB Small


See More: How to count multiple values

Report •


#1
September 7, 2009 at 08:45:18
Hi
For this you can use COUNTIF. Assuming the first "AAA Large" starts ic cell A1, =COUNTIF(A:A,A1) would count anything in column A that matches the text in A1. Simply substitue A1 for whichever cell you want to count the value of. Please note, this must be done in a cell that has no data already in it. i.e. if your list is just in colmn A, you can type the formula into a cell in column B.
Hope this helps.

Report •

#2
September 7, 2009 at 08:47:15
The function you want is SUMPRODUCT.

e.g. =SUMPRODUCT(($A$2:$A$10="AAA")*(B2:B10="Large"))

You can hard code the criteria into the formula like I did, or you can create a list of the unique items and sizes and reference that list.

Data...Filter...Advanced Filter will allow you to create list of the unique items for use with the function.


Report •

#3
September 7, 2009 at 16:04:46
Hi All,

Many thanks for the help. I used the SUMPRODUCT. It works beautifully.


Report •

Related Solutions


Ask Question