MrExcel Publishing
Your One Stop for Excel Tips & Solutions

summing like records

Posted by Colin on December 14, 2001 5:22 AM

I am working on a project and I need some way for excel to look through a list of 8000 records and if two or more rows have equal values in columns 1 and 2, then sum the values of column 3 into one row. The old cell contents can then be deleted or whatever.

I really need help with this one.



Posted by Tom Dickinson on December 14, 2001 6:26 AM

Try this:

Sub Macro1()
Dim Cnt1, Cnt2 As Integer
Cnt1 = 1
Do While Range("A" & Cnt1 + 1) <> Empty
Cnt2 = Cnt1 + 1
Do While Range("A" & Cnt2) <> Empty
If Range("A" & Cnt1) = Range("A" & Cnt2) And Range("B" & Cnt1) = Range("B" & Cnt2) Then
Range("C" & Cnt1) = Range("C" & Cnt1) + Range("C" & Cnt2)
Range("A" & Cnt2 & ":C" & Cnt2).Delete shift:=xlUp
End If
Cnt2 = Cnt2 + 1
Cnt1 = Cnt1 + 1
End Sub

I have assumed that the data starts in cell A1, and that there are no blanks. If there are blanks, you may want to switch from a "Do" loop to a "For" loop.

Good luck.

Posted by Mark W. on December 14, 2001 6:44 AM

If A1:C5 contains...


The array formula...


will produce 8.

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Juan Pablo G. on December 14, 2001 7:48 AM

Why not just


Juan Pablo G.

Posted by Mark W. on December 14, 2001 7:58 AM


...his conditional, "if two or more rows..."
requires an array formula anyway. And, I'm
reluctant to use SUMPRODUCT with only 1
argument since Help specifies that its
arguments should be "2 to 30 arrays..."
I'd be more inclined to use...

=SUMPRODUCT((A1:A5=B1:B5)+0,C1:C5) Why not just =SUMPRODUCT((A1:A5=B1:B5)*C1:C5) Juan Pablo G. : If A1:C5 contains... : {"A","B",1

Posted by Sunitha on December 14, 2001 8:16 AM

Re: Because...

I don't think this was my question. Somehow the worong question got pasted here. Can someone answer my question?