calculate sum based on the multiple selections from a reference table

I have a reference table, "Office" and like to get the total personnel based on the selection of offices in the "Allocations" table where I have the data validation configured to select multiple offices.

For example, if I selected "Austin" and "Tampa" in the cell of "Allocation" column, I will get the total personnel of 55 from the Office table. Please advise how to do this. Thanks in advance!!

Office	# of Personnel
Austin	15
Dallas	21
Las Vegas	16
Tampa	40
San Jose	28
Total	120

Allocations	Total Personnel
Austin, Dallas, Las Vegas	52
Austin, Tampa	55

Formulas and Functions

Lorenzo

Oct 10, 2024

MJCalvin

in F4, with 365:

=SUM(
  XLOOKUP(
    TEXTSPLIT( [@Allocations], ", " ),
    Office[Office],
    Office['# of Personnel],
    0
  )
)

sample.xlsx12 KB

3 Replies

Lorenzo
Silver Contributor
Oct 10, 2024
MJCalvin

in F4, with 365:

=SUM( XLOOKUP( TEXTSPLIT( [@Allocations], ", " ), Office[Office], Office['# of Personnel], 0 ) )

sample.xlsx12 KB
- MJCalvin
  Copper Contributor
  Oct 10, 2024
  Lorenzo, Awesome that worked!!! Thank you for the quick solution!!!
  - Lorenzo
    Silver Contributor
    Oct 10, 2024
    MJCalvin
    You're welcome & thanks for providing feedback
    (next times don't forget to mention the version of Excel you run otherwise risk exists you get proposals that won't apply)

Forum Discussion

calculate sum based on the multiple selections from a reference table

3 Replies

Resources