SUMIFS Issue

Good day. I am trying to build a grant spend-down sheet for my work. We have multiple grants and want all the spend-down data on one sheet. All of our data is being inputted on a different sheet though so I am trying to use the SUMIFS for our referencing. It keeps showing as unparseable. Here is the formula I am using, =SUMIFS({Grant Spend Down Data Entry Sheet [Amount]@row},{Grant Spend Down Data Entry Sheet [Grant Name]@row},=[Grant Name]@row,{Grant Spend Down Data Entry Sheet [Category]@row},=[Grant Category]@row,). I am still fairly new to smartsheets and formulas in general so any help would be appreciated.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Erik L

    The comma at the end of your formula will cause an issue. Try removing that as a first step.

    The way you have referenced your cells could also be a problem.

    [Amount]@row refers to the value in the Amount column in the current row of the current sheet.

    {Grant Spend Down Data Entry Sheet} is a cross sheet reference to another cell, or column, within a different sheet. You set this up by clicking on the Reference Another Sheet link when writing your formula.


    You shouldn't combine the two and was actually surprised I was able to use [] and @row in cross sheet reference names when I tried to replicate what you are doing.

    What is strange, is that when I named my cross sheet references as you did, the formula did not work (unparseable). When I did exactly the same but used names without @row and [ and ] in them, like this, it worked fine:

    =SUMIFS({Data Entry Sheet Amount}, {Data Entry Sheet Grant Name}, =[Grant Name]@row, {Data Entry Sheet Category}, =Category@row)

    However, I was then able to edit my cross sheet reference names to the names you used and it did continue to work. I wouldn't recommend this though.

    Cross sheet formulas are explained here: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Erik L

    The comma at the end of your formula will cause an issue. Try removing that as a first step.

    The way you have referenced your cells could also be a problem.

    [Amount]@row refers to the value in the Amount column in the current row of the current sheet.

    {Grant Spend Down Data Entry Sheet} is a cross sheet reference to another cell, or column, within a different sheet. You set this up by clicking on the Reference Another Sheet link when writing your formula.


    You shouldn't combine the two and was actually surprised I was able to use [] and @row in cross sheet reference names when I tried to replicate what you are doing.

    What is strange, is that when I named my cross sheet references as you did, the formula did not work (unparseable). When I did exactly the same but used names without @row and [ and ] in them, like this, it worked fine:

    =SUMIFS({Data Entry Sheet Amount}, {Data Entry Sheet Grant Name}, =[Grant Name]@row, {Data Entry Sheet Category}, =Category@row)

    However, I was then able to edit my cross sheet reference names to the names you used and it did continue to work. I wouldn't recommend this though.

    Cross sheet formulas are explained here: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

  • Erik L
    Erik L ✭✭✭

    Thank you so much. I got it to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!