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
-
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
-
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
-
Thank you so much. I got it to work
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!