Vlookup & Count Function

HI, EXCEL Formula Question:

I have an array which is a list of postal codes, and a count number in the adjacent column.

I'm looking to write a vlookup function which will return the total sum, for the specific for the first three characters.

e.g. dataset:
K1N 6B4 31
K0A 2W0 30
K0A 1A0 26
K0A 1W0 20
K2G 1V8 19
K2T 1H2 17


So, "KOA" = 76 (30+26+20).

Hi
you can use the following formula

Formula:

Please Login or Register  to view this content.

Not clear exactly what you want.

Assuming this data is in the range A2:B7, then in C2 enter this formula and copy down.

=SUMPRODUCT(--((LEFT($A$2:$A$7,3)=LEFT(A2,3))*$B$2:$B$7))

You will find the totals are calculated - (you will have 76 next to all the KOA codes).

Please advise if you want more sophistication!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

=SUMPRODUCT(--((LEFT($A$2:$A$7,3)=LEFT(A2,3))*$B$2:$B$7))

works perfectly! Thanks David!

Hi,

It seems the forumula only works on a smaller dataset.