CODEWITHSUNDEEP
excelformula
rjbogz
rjbogz

Reputation: 870

Find count of items in column and add the values from another column

I have two columns in a table (A and B). Column A has an item name and Column B has a quantity. I want to sum the quantities for all the items that have the same name. Example:

Item        |  Quantity
------------+----------
Red Hat     |         3
Green Shirt |         9
Red Hat     |         4
Blue Pants  |         6
Blue Pants  |         3
Green Shirt |        12

and I would like to add a column to the table that has this:

Item        |  Quantity  |  Total
------------+------------+-------
Red Hat     |         3  |      7
Green Shirt |         9  |     21
Red Hat     |         4  |      7
Blue Pants  |         6  |      9
Blue Pants  |         3  |      9
Green Shirt |        12  |     21

I want to be able to sort the table by the items that have the most total quantity (keeping it grouped by the item name, so if two items have the same total quantity, I would like them grouped separately).

Upvotes: 1

Views: 9600

Answers (1)

Stephen Hewlett
Stephen Hewlett

Reputation: 2445

Try the SUMIF function. See http://www.techonthenet.com/excel/formulas/sumif.php for more details.

More specifically, the formula for cell C1 should be:

=SUMIF(A:A,A1,B:B)

Assuming columns A and B are item and quantity respectively.

enter image description here

Upvotes: 1

Related Questions