Finding the Sum of the Values Within Unique Cells Based on 2 Criteria in a Range

[psmi123]

I want to find the sum of the range within the sector within the zone but only summing unique values. For example, I want to find the sum of the Codes within Sector Ice Cream, Zone 1. However I don't want to count duplicates within that range. $63 Dollars should be the total of Sector Ice Cream, Zone 1 because it removes duplicate values within that range. $103 should be the total of Sector Ice Cream, Zone 5. What formula would be the best way to do this. Thank you in advance for your help!




[TABLE="width: 785"]
<colgroup><col span="5" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"][TABLE="width: 785"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Sector[/TD]
[TD]Zone[/TD]
[TD]Code 1 $[/TD]
[TD]Code 2 $[/TD]
[TD]Code 3 $[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]1[/TD]
[TD]$30[/TD]
[TD]$20[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]1[/TD]
[TD]$20[/TD]
[TD]$30[/TD]
[TD]$8[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]2[/TD]
[TD]$40[/TD]
[TD]$26[/TD]
[TD]$46[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]3[/TD]
[TD]$24[/TD]
[TD]$74[/TD]
[TD]$27[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]4[/TD]
[TD]$94[/TD]
[TD]$27[/TD]
[TD]$83[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]5[/TD]
[TD]$55[/TD]
[TD]$5[/TD]
[TD]$43[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]5[/TD]
[TD]$5[/TD]
[TD]$55[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]5[/TD]
[TD]$43[/TD]
[TD]$5[/TD]
[TD]$55[/TD]
[/TR]
[TR]
[TD]Frozen Yogurt[/TD]
[TD]1[/TD]
[TD]$3[/TD]
[TD]$2[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Frozen Yogurt[/TD]
[TD]1[/TD]
[TD]$3[/TD]
[TD]$4[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]Frozen Yogurt[/TD]
[TD]2[/TD]
[TD]$4[/TD]
[TD]$3[/TD]
[TD]$2[/TD]
[/TR]
[TR]
[TD]Frozen Yogurt[/TD]
[TD]3[/TD]
[TD]$5[/TD]
[TD]$6[/TD]
[TD]$8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub UniqueZoneAmounts()
  Dim Coff As Long, LastRow As Long, LastCol As Long, Ar As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Range("B2:B" & LastRow).Value = Evaluate("IF(B2:B" & LastRow & "=B1:B" & LastRow - 1 & ","""",B2:B" & LastRow & ")")
  On Error GoTo NoDupes
  Application.ScreenUpdating = False
  For Each Ar In Range("B1:B" & LastRow).SpecialCells(xlBlanks).Areas
    For Coff = 1 To LastCol - 2
      Ar.Offset(-1, Coff) = Application.Sum(Ar.Offset(-1, Coff).Resize(Ar.Count + 1))
    Next
  Next
  Range("B2:B" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
NoDupes:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[psmi123]

Is there a formula instead that I can use?

 

[Rick Rothstein]

Is there a formula instead that I can use?

Probably, but I am not much of a formula guy and I would expect the series of formulas that you would need to be somewhat complex (you have to retrieve the unique Zones and their sectors, then you have to sum up the values from each code for the zones whether duplicate or not. I am sure one of our expert formula volunteers will eventually weigh in with a solution for you.