Conditional sum

[oxygn]

Hi All,

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

I have data in 6 columns like this:
[TABLE="width: 601"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD] Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]25-Jan-11
[/TD]
[TD] 1RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25-Feb-11
[/TD]
[TD] 1RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25-Feb-11
[/TD]
[TD] 2RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25-Feb-11
[/TD]
[TD] 2RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]25-Mar-11
[/TD]
[TD] 1RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76

[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 601"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD] Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]25-Jan-11
[/TD]
[TD] 1RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25-Feb-11
[/TD]
[TD] 1RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25-Feb-11
[/TD]
[TD] 2RENTAL
[/TD]
[TD]8
[/TD]
[TD]3763.2
[/TD]
[TD]4139.52
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]25-Mar-11
[/TD]
[TD] 1RENTAL
[/TD]
[TD]4
[/TD]
[TD]1881.6
[/TD]
[TD]2069.76
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Servando]

And what about a pivot table?, have you tried it? or that does not work for you?

 

[oxygn]

Sorry I dont know how use a pivot table. Also the my boss hates them and needs the results in a simpler format. Thanks

 

[dermie_72]

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

 

[Servando]

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

 

[JoeMo]

Here's a macro solution if you're willing to use macros. Assumes data begin in A2.

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

 

[oxygn]

Thanks dermie_72 but that does not help, it fails to sum all of unique combinations, missed some and then there are duplicates.

 

[dermie_72]

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.

 

[Peter_SSs]

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.

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"

 

[oxygn]

Thank you all for your time and help. Peter and Dermie your solutions really helped.

Regards,
oxygn