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 offi...

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

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

Resources