How do I correctly use Sum & Value formulas together?
I'm trying to get the total monthly parking charges for my office.
I have a column showing each individual's monthly parking charges, if applicable (enabled or disabled).
For the sheet summary, using =sum([Monthly Cost]1:[Monthly Cost]84) doesn't return anything but 0 because the column is using text.
I've tried adding the VALUE function but keep getting errors--the formula works on a single cell but not if I try dragging more than one.
Any suggestions or help?
Best Answer
-
Hey @PAS at Vista
Remove the quotations around the 299 so it reads like below. Also remove the numbers from the Estimated Monthly Cost so it will grab all future items in that row.
=IF([Parking Enabled?]@row="Enabled",299,"")
=SUM([Monthly Cost]:[Monthly Cost]
If you are feeling extra spicy you can move the 299 into the sheet summary as Parking Cost and swap out the 299 with [Parking Cost]#
Don't forget to apply the currency format the Cost columns as well.
Answers
-
Hey @PAS at Vista
Remove the quotations around the 299 so it reads like below. Also remove the numbers from the Estimated Monthly Cost so it will grab all future items in that row.
=IF([Parking Enabled?]@row="Enabled",299,"")
=SUM([Monthly Cost]:[Monthly Cost]
If you are feeling extra spicy you can move the 299 into the sheet summary as Parking Cost and swap out the 299 with [Parking Cost]#
Don't forget to apply the currency format the Cost columns as well.
-
Devin,
Thank you sooooooo much!!
I spent so much time trying to do this by myself by just staring and trying and trying---your help totally worked!
Can't wait to wow em at work with this. :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!