Using an array to sum the values of associated category

[KingDragon22]

Hey Folks,

I'm having a problem trying to sum these values. I could easily do it using VBA, however I'd like to avoid it and use excel functions if possible.

So I have the following tables that I'd like to essentially consolidate. This is of course, a gross over simplification of my table.

Client_ID Purchase
AA $10
AB $20
AC $30
AD $40
BA $50
BB $60
BC $70
BD $80

I also this:

Client Client_ID
A AA
A AB
A AC
A AD
B BA
B BB
B BC
B BD

What I would like to do is have this more consolidated table:

Client Purchase
A sum all client_ID purchases associated with A
B sum all client_ID purchases associated with B

I would like this table to lookup all associated client IDs associated with, say A, then find those IDs in the first table, and sum up the purchase amounts.

Thanks folks!

[TheCman81]

Hi there, From the attached sheet can you supply expected results? This should be q simple SUMIF but feel there may be more to it

KingDragon22 v1.xlsx

Thanks

[KingDragon22]

I've created the full effect of it.

KingDragon22 v1.xlsx

One thing to note is that the client ID's are not always in order. They may be scattered and be 100+ cells apart. So the 3rd sheet "Client Purchases" needs to be find all Client IDs that are associated with the Client and sum all the purchases associated with the Client IDs that are associated with the client.

Thanks!

[TheCman81]

Sorry for the delay.

I've done 2 formulas you can choose from.

Formula1 - This is used with a helper column in Client_ID Purchases tab

Formula2 - Doesn't use a helper column, this is an array formula and will need to be entered with Ctrl Shift Enter {} you should see these curly brackets if you've done it correctly (DONT manually type these brackets in)


KingDragon22 v2.xlsx