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!

 

[Fluff]

Are your Zip codes either all text or all numbers?
Looking at your image col A is left aligned, whereas col D is centred.

 

[davilillo]

Zip codes are all numbers. I manually centered column D, but forgot to do the same with column A. Also, the Number format in all of them is General.

 

[Scott T]

When you debug what line is highlighted?

 

[Fluff]

This is what I got with some test data

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Postcode	Altitude		Postcode	County		County	Total
2	90004	2.51		90002	Bedfordshire		Bedfordshire	132.17
3	90005	3.6		90001	Bedfordshire		Berkshire	180.12
4	90004	5.3		90005	Bedfordshire		Bristol
5	90011	80.12		90004	Bedfordshire		Buckinghamshire	258.1
6	90002	17.2		90011	Berkshire
7	90013	13.1		90010	Buckinghamshire
8	90011	100		90012	Buckinghamshire
9	90012	101		90013	Buckinghamshire
10	90004	7.56
11	90005	79
12	90002	17
13	90012	73
14	90013	71
Input

Looking at the image in post#1, in an empty cell put
=a2=d4
and in another
=e4=g20
what do they say?

 

[davilillo]

=a2=d4 returns FALSE;
=e4=g20 returns TRUE.

 

[davilillo]

When you debug what line is highlighted?

Line 14, with the following text highlighted:
If Application.VLookup(Cells(x, "A"), Range("D2:E" & slr), 2, 0) = Cells(y, "G") Then

 

[Fluff]

@davilillo
Please do not quote whole posts (when they are large) as it just clutters up the thread. Post#16 is fine as the quote is small.
should the values in A2 & D4, be the same?
If so they aren't, hence the problems.

 

[davilillo]

Sorry about that. How come it is showing FALSE, if both numbers are the same? Perhaps as a result of different formats?

 

[Fluff]

They aren't the same, check for leading/trailing spaces.
Also try
=ISTEXT(A2)
=ISTEXT(D4)

 

[Scott T]

This could be because you have Total in column A and the Vlookup will not find it. This will skip any unfound zips from column A instead of giving an error.

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 UCase(Cells(x, "A")) <> "TOTAL" Then
        On Error Resume Next
            If Application.VLookup(Cells(x, "A"), Range("D2:E" & slr), 2, 0) = Cells(y, "G") And UCase(Cells(x, "A")) <> "TOLTAL" Then taxcol = taxcol + Cells(x, "B")
        End If
    Next x
    Cells(y, "H") = taxcol
Next y
End Sub