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

davilillo

New Member
Joined
Jan 9, 2019
Messages
14
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:

olpeWKq

BvMsIBL.png



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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Last edited:
Upvote 0
Try

Code:
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
 
Upvote 0
Another option
Code:
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
 
Upvote 0
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?
 
Upvote 0
Here's a visual representation of what I'm talking about:

uFocVdj.png


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


73zcpZZ.png


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.
 
Upvote 0
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

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

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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