Sum all values if zip code matches county (3 different tables)

[davilillo]

Hello everyone,

My name is David and I've been following the forum for a while. I've learned a lot but never got to write a post, so here I go!

I'm trying to calculate the California Sales Tax that I have to remit to the state by county (aargh!), and have 3 tables:

1. Zip code and tax collected per order
2. Zip code and county it's located at
3. Total sales tax collected per county

The top of the table looks like this:

What I'm trying to do is match the zip code of each order (first 2 columns) with their respective county (middle 2 columns), and then add them all up to reflect the total sales tax collected per county (last 2 columns). I've been at it for a while but can't figure it out. Thanks a lot beforehand for your help!

 

[steve the fish]

Try in H2:

=SUMIF(E:E,G2,B:B)

 

[Special-K99]

Try

in H2
=SUMPRODUCT((E$2:E$1000=G1)*(A$2:A$1000=D$2:D$1000)*(ROW(B$2:B$1000)))
and copy down the column

Steve's answer is better

 

[Scott T]

Try

Sub totaltax()
Dim flr As Long
Dim slr As Long
Dim tlr As Long

flr = Cells(Rows.Count, "A").End(xlUp).Row
slr = Cells(Rows.Count, "D").End(xlUp).Row
tlr = Cells(Rows.Count, "G").End(xlUp).Row

For y = 2 To tlr
    taxcol = 0
    For x = 2 To flr
        
        If Application.VLookup(Cells(x, "A"), Range("D2:E" & slr), 2, 0) = Cells(y, "G") Then taxcol = taxcol + Cells(x, "B")
    Next x
    Cells(y, "H") = taxcol
Next y

End Sub

 

[Fluff]

Another option

Sub davilillo()
   Dim Cl As Range
   Dim CyDic As Object, ZpDic As Object
   
   Set CyDic = CreateObject("scripting.dictionary")
   Set ZpDic = CreateObject("scripting.dictionary")
   CyDic.CompareMode = 1
   ZpDic.CompareMode = 1
   For Each Cl In Range("G2", Range("G" & Rows.Count).End(xlUp))
      CyDic(Cl.Value) = Empty
   Next Cl
   For Each Cl In Range("D2", Range("D" & Rows.Count).End(xlUp))
      ZpDic(Cl.Value) = Cl.Offset(, 1).Value
   Next Cl
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      CyDic(ZpDic(Cl.Value)) = CyDic(ZpDic(Cl.Value)) + Cl.Offset(, 1).Value
   Next Cl
   For Each Cl In Range("G2", Range("G" & Rows.Count).End(xlUp))
      Cl.Offset(, 1).Value = CyDic(Cl.Value)
   Next Cl
End Sub

 

[davilillo]

Thanks for your responses! Steve, your formula seemed worked well, although the sum of the total sales tax collected by county differed from the sum of the tax collected for each order by more than $1,000. Maybe because the table with all the orders contain 3625 rows and the list of zip codes only goes down to 2658?

 

[davilillo]

Here's a visual representation of what I'm talking about:

This is the sum of the sales tax collected in each order, which adds up to $4,863.28.

And this is the total sum of the sales by county, after having calculated them using the formula that Steve suggested. Any idea of why they don't match?

I'm going to try the suggested VBA codes as well, although I don't have any previous VBA experience, so I'll have to do some research first.

 

[davilillo]

Just tried both VBA suggested solutions:

-Scott's code gave me a Run-time error 13
-Fluff's code erased all of the calculations in column H

 

[Fluff]

My code should have put the totals in col H, are you saying that it didn't?

 

[davilillo]

Right. After running it, Col H is blank.