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.

Thanks,

Colin


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
Loop
Cnt1 = Cnt1 + 1
Loop
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...

{"A","B",1
;"B","C",2
;"C","C",3
;"A","B",4
;"B","B",5}

The array formula...

{=IF(SUM(($A$1:$A$5=$B$1:$B$5)+0)>1,SUM(($A$1:$A$5=$B$1:$B$5)*$C$1:$C$5),0)}

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

=SUMPRODUCT((A1:A5=B1:B5)*C1:C5)

Juan Pablo G.

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

Because...

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