Summing duplicates in Excel

Question

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

I sorted the list and summarized them for each product, but I just used the sum function, I felt that there should be a formula that uses the IF statement to find records that are equal to each other, and then summarize their corresponding quantitative lines but I cannot understand this.

What I'm trying to get is IF (any field in column A = 3792, then sums up all the related quantitative 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

Excel SUMIF () -Formula can help you accomplish this.

as described syntax

 SUMIF(range, criteria, sum_range) 

Searches for fields within a range that match the criteria and sum the values ​​in sum_range (at the same index where the criteria was found in the range, respectively).

you may need to create a matrix for each quantity / search criterion to summarize and compare ...

Another approach :

For a specific column (let’s take C) create a temporary column and add a value of 1 to each row.

For example, in the "Amount A:" column, enter the formula:

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

While D1 is the cell containing the number 3792 (above), and 2 in C2 is the current row in which the formula is located.

You could just drag it to the right and bottom.

This will be the result:

 ABCDEFG 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 

Just just summarize at the end and you get your results. If you need to add another number than 1 , just add another column containing the corresponding value, then replace it with IF formula.

you may need to automate this even more by using HLOOKUP() for the widget number at the top.

considers

Answer 2

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

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

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

Answer 3

Since you have a sorted list, I would use the built-in SUBTOTAL function instead of the formula. Select all of your data, then go to Data> Subtotal. Then configure it based on the product change.

Another option is to create a pivot table that will summarize the elements. I am a big fan of storing resumes and data separately.

Answer 4

The original post was from 7 years ago, but here is my approach anyway using a little more brute force.

I took all the “Column A” and copied it to a new sheet (either to the right or somewhere to the side). Highlighted the copied dataset and deleted duplicates, leaving a new list of only one instance of the values ​​from "Column A". Lets call this new dataset "Column D". Then let me output the summary column "Column E"

Then I used SUMPRODUCT in combination with SUMIF in "Column E" to get this formula:

Column Value E = SUMPRODUCT (SUMIF ( Source Column A Range , Suitable Value in Column D , Original Column Ranges C ))

This summarizes all instances and displays the total.

The obvious problems with this are results that will not be automatically updated to include new instances of Column A, but if you have an existing dataset that you want to extract, it can be useful as a quick fix.

Jd

Answer 5

Excel can automatically handle this using the consolidation tool in the data tool section of the data feed tab.

Just create a new sheet, and then indicate your range of cells with graphs, check the box on the left of the column, and it should give you a unique consolidated account.

Answer 6

I summarize the duplicates like this:

Say you have something like this

  ABD France Apples 15 Spain Potatoes 15 France Apples 5 Spain Potatoes 1 

I would do (in column D):

  =A1&"-"&B1 

Then copy column D to column E and delete duplicates.

In column F:

  =SUMPRODUCT((D$2:D$5=E2)*C$2:C$5) 

So you should have in column E:

  DE France-Apples 20 Spain-Potatoes 16