Index collect formula

michelle.farsund-hall91806

Can anyone help me with a formula? I am trying to sum a column from another sheet based on what is selected in a multi select dropdown. I worked with the Pro Desk at Smartsheet and they provided me with this formula, however its not working . Perhaps I have parenthesis or comma off someplace??

=IFERROR(SUM(INDEX({Team Revenue PY and YTD 2023 -2023 YTD}, COLLECT(HAS(@cell, [Producer/Team Name(s)]@row, {Team Revenue PY and YTD 2023 Prod/Team Name}), 0))), "")

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

If you are trying to add multiple cells together (sum) based on criteria in other columns (ifs), then you would need a SUMIFS.

https://help.smartsheet.com/function/sumifs

michelle.farsund-hall91806

Thanks Paul, I tried SUMIFS, but perhaps do I have something wrong below?

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

Paul Newcome

You don't need the INDEX or COLLECT or IFERROR functions. Just the SUNIFS following the suggested syntax.

michelle.farsund-hall91806

Sorry perhaps I am not explaining this well. I need it to Sum a column in a different sheet based on the selections contained in a muti select dropdown within my sheet. some dropdown selections include 1 entry, some include 4. If 4 entries are selected then I need them to add all 4 team totals together..

Paul Newcome

Yes. You still need the SUMIFS, and you still don't need the INDEX, COLLECT, or IFERROR. You will need the HAS function though.

=SUMIFS({range to sum}, {criteria range}, HAS(@cell, "text string"))

michelle.farsund-hall91806

I am not sure what you mean by "text string"?

Paul Newcome

Whatever string you are looking for in the multi-select dropdown.

michelle.farsund-hall91806

ok still not understanding exactly what you mean, so this cant be set up as a column formula..?

Paul Newcome

It can be a column formula. I'm not sure I understand your question. Have you tried creating the formula yet?

michelle.farsund-hall91806

Here's what I have =SUMIFS({{Team Revenue PY and YTD 2023 -2023 YTD}}, {{Team Revenue PY and YTD 2023 Prod/Team Name}, HAS(@cell, "*Christopher Black*James GravesBusch/ScaringeWoods/Morris"))

Paul Newcome

And is it giving an error or an unexpected number?

michelle.farsund-hall91806

=SUMIFS({Team Revenue PY and YTD 2023 -2023 YTD}, {Team Revenue PY and YTD 2023 Prod/Team Name}, HAS(@cell, "*Christopher Black*James GravesBusch/ScaringeWoods/Morris"))

This formula gave me a 0

Paul Newcome

In that case there are no cells that contain your text string. Double check your spelling to ensure there is at least one entry in at least one cell that matches exactly.

michelle.farsund-hall91806

In this case someone selected 4 choices in the drop down, not sure how the text string should look exactly?

however every row will have different data entered.

=SUMIFS({Team Revenue PY and YTD 2023 -2023 YTD}, {Team Revenue PY and YTD 2023 Prod/Team Name}, HAS(@cell, "*Christopher Black*James GravesBusch/ScaringeWoods/Morris"))

michelle.farsund-hall91806

on the source sheet, they each are in different rows with different totals