SUMIFS Issue

Erik L

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.

KPH

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

KPH

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

Thank you so much. I got it to work