Use Sumifs and Index Match to add values in a multiselect lookup table

TomFS

Hi there,

I've followed some input (as best I could figure out) from another help column to attempt to add up analyses costs from a multiselect column. I'll add pictures as best I can.

Process for use of sheet:

1.) Enter sample ID into a database / flat file, called "Test Tracker"

Image of the table "Test Tracker"

2.) Select analyses from multiselect column (Test Name), also on the sheet "Test Tracker"

3) ... the bit I can't do - a formula adds up the values of the analyses selected in a cost cell. The values of the analyses are in a lookup table called "Test Costs".

Image of the table "Test Costs":

The formula I have can successfully look up one value (like a vlookup) but generates the fault #NO MATCH if I select multiple tests.

This formula look like this:

=SUMIFS({Test Costs Range 2}, {Test Costs Range 3}, CONTAINS(@cell, INDEX({Test Costs Range 1}, MATCH([Test Name]@row, {Test Costs Range 3}, 0), 1)))

I'd REALLY appreciate any thoughts / assistance / input please!

Cheers,

Tom

Paul Newcome

Try this...

=SUMIFS({Test Costs Sheet Price Column}, {Test Cost Sheet Test Name Column}, HAS([Test Name]@row, @cell))

Paul Newcome

Try this...

=SUMIFS({Test Costs Sheet Price Column}, {Test Cost Sheet Test Name Column}, HAS([Test Name]@row, @cell))

TomFS

Genius! you, sir are a true gentleman! many thanks for your time here Paul

Paul Newcome

Happy to help. 👍️