Formula to return the SUM of prices from a cell with multiple values (multiselect column)

montserrat

Hello!

I'm sure someone has encountered this issue before so hopefully there's a work around it.

We have a tracker with multiple service requests. We document each request in a very detailed way here. We already have a lot of columns here, so adding more than 2-3 is not an option.

From a multi-select column named "Transaction", users can select from a long list of transactions names as many as they need. Each transaction has a different price. I need to build a formula that SUM the prices of all the transactions selected, searching in a helper sheet for the price associated with each transaction.

Is there a mix of functions that could help me achieve this? I was thinking that maybe COLLECT with VLOOKUP but not sure if they work together.

Any guidance would be wonderful.

Thanks!

Paul Newcome

You will need to incorporate the HAS function to get it to sum from multiple rows on a reference sheet based on a single cell that is multi-select dropdown.

=SUMIFS({Reference Sheet $$ Column}, {Reference Sheet Selection Column}, HAS([Selection Column]@row, @cell))

JamesB

@montserrat

You should be able to use a SUMIF

=SUMIF({Helper Sheet Range 1},=Transaction@row,{Helper Sheet Range 2})

Helper Sheet Range 1 = Transaction Names

Helper Sheet Range 2 = Number to add up

Paul Newcome

You will need to incorporate the HAS function to get it to sum from multiple rows on a reference sheet based on a single cell that is multi-select dropdown.

=SUMIFS({Reference Sheet $$ Column}, {Reference Sheet Selection Column}, HAS([Selection Column]@row, @cell))

montserrat

@Paul Newcome

OMG! You have just simplified an issue that I've been dealing with for months. I was sure this was impossible and focused on converting strings into numbers. How wrong I was!

THANK YOU SO MUCH! Your solution worked perfectly.

Paul Newcome

Happy to help. 👍

Keep in mind though... The only reason this works is because you are using the multi-select dropdown. Anything else does add more complexity.