Sum all rows in excel with the same value in a certain column

Question

So basically I will have a changing list of names entered like so

Deborah     9   30

Steven      4   22

Michelle    9   26

Michelle    8   30

Alice      10   28

John        3   21

David       7   23

David       9   26

David       7   24

Lucy        6   24

and my goal is to write a macros so that the names appear like this

Deborah     9   30

Steven      4   22

Michelle    17  56

Alice       10  28

John        3   21

David       23  73

Lucy        6   24

so all the rows with the same value for column 1 will be a sum of the values in the other column and consolidated to one row. The names are going to be changing, so I cant hardcode in IF something = "Michelle" it has to just be IF these rows = these other rows. I am trying to automate a tedious task at work, thanks for any suggestions!

Probably you'd like too see this . http://stackoverflow.com/questions/18870378/group-by-sum-in-excel — Hima, Jul 18 '14 at 15:24

score 0 · Answer 1 · answered Jul 18 '14 at 15:37

You can create a distinct sorted list of names using an array formula with INDEX, MATCH, MAX and COUNTIF as described here:

http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/

Then you can just use a simple SUMIF function for each name in the result.

score 0 · Accepted Answer · answered Jul 18 '14 at 15:37

If the duplicated names are in consecutive rows, then try this short macro:

Sub DeDup()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = N To 2 Step -1
        If Cells(i, 1) = Cells(i - 1, 1) Then
            Cells(i - 1, 2) = Cells(i - 1, 2) + Cells(i, 2)
            Cells(i - 1, 3) = Cells(i - 1, 3) + Cells(i, 3)
            Range(Cells(i, 1), Cells(i, 3)).Delete Shift:=xlUp
        End If
    Next i
End Sub

score 0 · Answer 3 · answered Jul 18 '14 at 16:27

0

Just use a sumif formula. you can manipulate it to your needs. It will sum what you want in a given row. Or use an array if you would like.

answered Jul 18 '14 at 16:27

acoopie89

13
4

Sum all rows in excel with the same value in a certain column

3 Answers3