Cross-Sheet SUM formula roadblock -- any formula wizards who can help?

Hello!

I'm currently using a cross-sheet formula to pull in multiple cells of numeric information in a column on 1 sheet and return as 1 cell in another. I'm doing this using a JOIN(COLLECT formula.


=JOIN(COLLECT({Dev Spend Auspice Tracker - Development Fees}, {Dev Spend Auspice Tracker - ID}, ID@row), SUBSTITUTE(HELPER#, "-", ""))


That part is working, but what I actually need is a formula to sum these numbers, not roll them up.


My guess is that I'm not able to sum off of the formula column as the formula column is no longer recognizing the value as distinct #s.


Hoping someone here might be able to help me tweak my JOIN(COLLECT cross-sheet formula to return the information as a SUM, instead of a rolling all cells together into 1?


For more clarity, the formula is looking at a sheet that has many projects, each with their own distinct ID, but there can be multiple rows associated with each project, and so multiple rows using the same ID. I'm trying to lump together only the costs associated with rows sharing the same ID.


(I've pasted the formula as-is; I know the SUBTITUTE(HELPER function is what is creating the rollup view, but my efforts to delete that part and write in SUM have not worked)


Any help would be SO GREATLY APPRECIATED!!!


Thank you,

Laurka

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try a SUMIFS.

    =SUMIFS({Dev Spend Auspice Tracker - Development Fees}, {Dev Spend Auspice Tracker - ID}, ID@row)


    Also... It seems as if you are using the SUBSTITUTE function as the delimiter to possibly create a line break? If so, you can get rid of the helper column and replace the SUBSTITUTE with CHAR(10). This will insert the line break without the need for a helper column.

    =JOIN(COLLECT({Dev Spend Auspice Tracker - Development Fees}, {Dev Spend Auspice Tracker - ID}, ID@row), CHAR(10))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try a SUMIFS.

    =SUMIFS({Dev Spend Auspice Tracker - Development Fees}, {Dev Spend Auspice Tracker - ID}, ID@row)


    Also... It seems as if you are using the SUBSTITUTE function as the delimiter to possibly create a line break? If so, you can get rid of the helper column and replace the SUBSTITUTE with CHAR(10). This will insert the line break without the need for a helper column.

    =JOIN(COLLECT({Dev Spend Auspice Tracker - Development Fees}, {Dev Spend Auspice Tracker - ID}, ID@row), CHAR(10))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you, Paul Newcome!!

    Both of your formulas work perfectly. And I feel pretty silly for not thinking of trying SUMIFS... sometimes I fail to see for the forest for the trees.

    You've saved hours of strife on my end, I truly appreciate it!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!