SUM and INDEX combination formula
I have this formula that works in Excel, but when I try to rebuild it in Smartsheet, I'm getting an error that it's unparseable. How can I do this in Smartsheet:
=SUM(C3:INDEX(C3:N3,$C$2))
I've gotten here in Smartsheet, but it doesn't work:
=SUM(January@row:INDEX(January@row:December@row, 1, $January$1))
I would like to add up the values in the purple row, starting in January and moving to the right as many boxes as are designated in the cell that is highlighted in blue (in this example, it should give me the sum of January, February and March).
Thank you for any ideas!
Best Answer
-
Ok. And are you able to possibly relocate the "3" into a different cell?
The easiest way to accomplish what you need is to utilize that top row. Basically you would put the month number in each column in the gray row (you can change text color to match cell fill color to essentially make the cells look blank). Then you would use a formula that looks like this:
=SUMIFS(January@row:December@row, January$1:December$1, @cell <= 3)
You would replace the 3 with a cell reference to wherever you are able to relocate it.
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!
Answers
-
Where exactly are you wanting to put this formula?
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!
-
-
Hi Paul, I would like it to be at the right end of row 2 - in the column beyond December. Thanks for your help, total Smartsheet noob here.
-
Ok. And are you able to possibly relocate the "3" into a different cell?
The easiest way to accomplish what you need is to utilize that top row. Basically you would put the month number in each column in the gray row (you can change text color to match cell fill color to essentially make the cells look blank). Then you would use a formula that looks like this:
=SUMIFS(January@row:December@row, January$1:December$1, @cell <= 3)
You would replace the 3 with a cell reference to wherever you are able to relocate it.
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!
-
Thank you, Paul! This worked! Appreciate you taking the time to answer and help.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!