Conditional sum

oxygn

New Member
Joined
May 24, 2010
Messages
25
Hi All,

I need help with an excel sheet where I have to conditional sum.

I have data in 6 columns like this:
Col A
Col B
Col C
Col D
Col E
Col F
1
25-Jan-11
1RENTAL
4
1881.6
2069.76
2
25-Feb-11
1RENTAL
4
1881.6
2069.76
2
25-Feb-11
2RENTAL
4
1881.6
2069.76
2
25-Feb-11
2RENTAL
4
1881.6
2069.76
3
25-Mar-11
1RENTAL
4
1881.6
2069.76


<tbody>
</tbody>
I want to do a conditional sum where we look for unique combination of Col A, B and C and do the sum of values for Col D, E and F
The end result should look something like this:
Col A
Col B
Col C
Col D
Col E
Col F
1
25-Jan-11
1RENTAL
4
1881.6
2069.76
2
25-Feb-11
1RENTAL
4
1881.6
2069.76
2
25-Feb-11
2RENTAL
8
3763.2
4139.52
3
25-Mar-11
1RENTAL
4
1881.6
2069.76

<tbody>
</tbody>

I have highlighted an example in bold. The unique combination of Col A:2, Col B: 25-Feb-11 and Col C: 2Rental will get the appropriate result.

Thanks for your help.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sorry I dont know how use a pivot table. Also the my boss hates them and needs the results in a simpler format. Thanks
 
Upvote 0
Try this:
In the table provided:

G1 = =IF(AND(A1=A2,B1=B2,C1=C2),1,0)
G2 - G5 = =IF(AND(A2=A3,B2=B3,C2=C3),1,IF(AND(A2=A1,B2=B1,C2=C1),1,0))
H1 = =IF($G1>0,SUMIFS(D$1:D$5,$A$1:$A$5,$A1,$B$1:$B$5,$B1,$C$1:$C$5,$C1),D1)
Copy down
I1 = =IF($G1>0,SUMIFS(E$1:E$5,$A$1:$A$5,$A1,$B$1:$B$5,$B1,$C$1:$C$5,$C1),E1)
Copy Down
J1 = =IF($G1>0,SUMIFS(F$1:F$5,$A$1:$A$5,$A1,$B$1:$B$5,$B1,$C$1:$C$5,$C1),F1)
Copy Down

If you don't want to see the duplicate details, then you would need to create a unique table linking to the main table, for the result.

Hope that helps
 
Upvote 0
Ok... Try this
G1] =IF($A1&$B1&$C1=$A2&$B2&$C2,"",A1)
I1] =IF($A1&$B1&$C1=$A2&$B2&$C2,"",B1)
J1] =SI($A1&$B1&$C1=$A2&$B2&$C2,"",C1)
Or copy G1 to I1:J1
K1] =IF($H1<>"",SUMPRODUCT(($A$1:$A$5=$H1)*($B$1:$B$5=$I1)*($C$1:$C$5=$J1)*D$1:D$5),"")
You can copy this to L1 and M1
Then you can copy the range G1: M1 down as far as necessary.


Servando Villalón
 
Upvote 0
Here's a macro solution if you're willing to use macros. Assumes data begin in A2.
Code:
Sub oxygn()
Dim lR As Long, R As Range, vA As Variant, dRws As Range

lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A2", "F" & lR)
vA = R.Value
For i = LBound(vA, 1) To UBound(vA, 1) - 1
    For j = i + 1 To UBound(vA, 1)
        If vA(i, 1) & vA(i, 2) & vA(i, 3) = vA(j, 1) & vA(j, _
            2) & vA(j, 3) Then
            vA(i, 4) = vA(i, 4) + vA(j, 4)
            vA(i, 5) = vA(i, 5) + vA(j, 5)
            vA(i, 6) = vA(i, 6) + vA(j, 6)
            For k = 4 To 6
                R.Cells(i, k).Value = vA(i, k)
            Next k
            If Not dRws Is Nothing Then
                Set dRws = Union(dRws, R.Rows(j))
            Else
                Set dRws = R.Rows(j)
            End If
        End If
    Next j
Next i
If Not dRws Is Nothing Then dRws.Delete shift:=xlUp
End Sub
 
Upvote 0
Thanks dermie_72 but that does not help, it fails to sum all of unique combinations, missed some and then there are duplicates.
 
Upvote 0
sorry to hear that Oxygn. I tested it on my system with your test data and it worked perfectly. I'm not sure if the problem related to the table format, or the sum fields. Either way, the macro provided below should simplfy the whole issue. Good luck.
 
Upvote 0
Here's a different macro approach. It assumes ..

1. Data in columns A:F
2. Headings in row 1
3. Columns G:M empty

Please test in a copy of your workbook.
Rich (BB code):
Sub oxygn2()
  Dim lr As Long
  
  Const f As String = "=SUMIFS(D$2:D$#,$A$2:$A$#,$H2,$B$2:$B$#,$I2,$C$2:$C$#,$J2)"
  
  Application.ScreenUpdating = False
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("G2").Formula = "=COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1"
  Range("A1:F" & lr).AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("H1")
  With Range("K2:M" & Range("H" & Rows.Count).End(xlUp).Row)
    .Formula = Replace(f, "#", lr, 1, -1, 1)
    .Value = .Value
  End With
  Columns("A:G").Delete
  Application.ScreenUpdating = True
End Sub


This code is written for Excel 2007 & later. If using an earlier version, or you need this to work on all versions then change the blue line of code to

Range("G2").Formula = "=SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),--(C$2:C2=C2))=1"
 
Upvote 0
Thank you all for your time and help. Peter and Dermie your solutions really helped.

Regards,
oxygn
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top