Index collect formula

Paul Newcome

So you want to add up all rows where they have at least one of those names?

michelle.farsund-hall91806

I want to find the 4 team names selected in my main sheet on my master sheet (each in a different row) and total them on my main sheet.

Paul Newcome

Are you able to provide screenshots for context?

michelle.farsund-hall91806

Here you go. So I need the row with ORL-Murphy-Wonus Team in the orange column to display total of $8,000 and I need the 2nd row with 4 teams selected to display their total of $75,000 for example.

Paul Newcome

Use a cell reference instead of a text string.

[Column name]@row

michelle.farsund-hall91806

I've tried everthing , I can't figure this out unfortunately.

=SUMIFS({Team Revenue PY and YTD 2023 -2023 YTD}, {Team Revenue PY and YTD 2023 Prod/Team Name}, HAS([Producer/Team Name(s)]@row))

Paul Newcome

You still need the first part of the HAS function. You're missing the @cell.

michelle.farsund-hall91806

This is returning a 0

=SUMIFS({Team Revenue PY and YTD 2023 -2023 YTD}, {Team Revenue PY and YTD 2023 Prod/Team Name}, HAS(@cell, [Producer/Team Name(s)]@row))

Paul Newcome

In your last set of screenshots, which one are you putting the formula in? The one with the multi-select, or the other one?

michelle.farsund-hall91806

The formula goes in the 1st screenshot in the orange highlighted column next to the multi select column.

Paul Newcome

Ok. In that case we need to switch the arguments around in the HAS function so the cell reference comes first and @cell second.

michelle.farsund-hall91806

Not sure I completely understand, here. Could you please send me what the formula should be?

Paul Newcome

You currently have

HAS(@cell, column name]@row)

You need

HAS([column name]@row, @cell)

michelle.farsund-hall91806

Oh my word! Yes, that's it!!!

=SUMIFS({Team Revenue PY and YTD 2023 -2023 YTD}, {Team Revenue PY and YTD 2023 Prod/Team Name}, HAS([Producer/Team Name(s)]@row, @cell))

Thank you so much Paul!

Paul Newcome

Happy to help. 👍️