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!

 

[davilillo]

Fluff, =ISTEXT(D4) returned false, so I converted all the zip code values in column D using formula =TEXT(D[#],"0"). I then tried your VBA code and it worked almost perfectly; total sum of column A differed from that of column H by about 15 bucks, but that should be good enough. I'm intrigued why, though.

Scott, your VBA code returned very high values in column H. Not sure why.

Thank you both very much for your assistance, anyway!

 

[davilillo]

I was going to start another thread, but I figured since this is related to the same question, I'd just follow up here. I have another spreadsheet that will probably give me more accurate information on collected sales tax, since it includes which county specifically each order's sales tax was collected for. See below:



To calculate the total sales tax collected per county in column M, I’d need a formula that:

1. Searches for the name of the corresponding county in column L on column G, in the same row where "County" appears on column F;
2. When it finds the county name, verifies that text "CA" appears two rows above, next to "State" (to prevent duplicates with other states)
3. Adds up all the "Total_Tax" values next to CA for that county

Again, thanks a lot in advance!

 

[Fluff]

total sum of column A differed from that of column H by about 15 bucks, but that should be good enough. I'm intrigued why, though.

I suspect that it's down to rounding errors & the way VBA handles rounding compared to Excel.

As to your next question, whilst they may be related, I feel that it's significantly different, so please start a new thread.

 

[davilillo]

Okay, thank you.

 

[Fluff]

You're welcome