Find count of items in column and add the values f

问题:

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).

回答1:

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.