Summing Duplicates in Excel

Question

I have a long list of names of products and numbers of those products, I want to combine all the duplicate entries and add their numbers. So if I have 2 entries for Widget X, and each entry has 3 products, then I want to combine this to one entry for Widget X with 6 products.

I've sorted the list out and summed them for each product but I just used a sum function, I felt there must be a formula that uses an IF statement to detect entries that equal each other and then sum their respective quantity lines but I can't figure it out.

What i'm trying to get is IF (any field in column A = 3792 , then sum all its associated quantity fields in column C)

Does anyone have any ideas?

3792    Widget A 1
3792    Widget A 1
3792    Widget A 1
3792    Widget A 1
3792    Widget A 1
3792    Widget A 2
3792    Widget A 1
3805    Widget B 1
3805    Widget B 1
3806    Widget C 8
3823    Widget D 895
3823    Widget D 1
3823    Widget D 20
3892    Widget E 2
3892    Widget E 1
3892    Widget E 1

Answer 1

Use the sumif function. See this blog for an explanation.

Assuming the values started in A1, paste this function into D1 and copy to D1-D16 to get sums for each field (there would of course be duplicates)

=SUMIF(A$1:A$16,A1,C$1:C$16)

Answer 2

Excel's SUMIF()-Formula might help you accomplish this.

as described, the syntax is

SUMIF(range, criteria, sum_range)

Searches fields within range that match criteria and sums up the values in sum_range (at the same index where the criteria has been found in range, respectively).

you might want to create a matrix for each number/search criteria to sum up and compare...

Another approach:

For a certain Column (let's take C) make a temporary column and add a 1 as value to each line.

For example, in Column "Sum A:" type the formula:

=IF($A2=D$1,1,0)

Whereas D1 is the cell containing the number 3792 (in top) and the 2 in C2 is the current line the formula stands in.

You would just be able to drag it to the right and to the bottom.

This would be the result:

A       B           C         D         E         F         G
                    3792      3805      3806      3823      3892
3792    Widget A    1
3792    Widget A    1
3792    Widget A    1
3792    Widget A    1
3792    Widget A    1
3792    Widget A    1
3792    Widget A    1
3805    Widget B              1
3805    Widget B              1
3806    Widget C                       1
3823    Widget D                                  1
3823    Widget D                                  1
3823    Widget D                                  1
3892    Widget E                                            1
3892    Widget E                                            1
3892    Widget E                                            1

SUM:                7         2        1          3         3

You just simply sum up at the end and you got your results. If another number should be added than 1, simply add another column containing the appropriate value, you then replace it in the IF-formula.

you might want to automate this even more using a HLOOKUP() for the widget number's at the top.

regards

Answer 3

Since you have the list sorted, I would use the builtin SUBTOTAL feature instead of a formula. Select all your data then goto Data > Subtotal Then have it based off of change in product.

The other option is to create a pivot table that will sum the items. I'm a big fan of keeping summary and data separate.

Answer 4

A PivotTable should do the job - see here