SUMIF and Absolute Value
Hello,
I want to sum up the Project Remaining Hours of all "In Progress" projects. Sometimes I have negative project remaining hours, but I don't want to add the negative value.
How do I sum up the absolute value of the Project Remaining Hours in the formula below?
=SUMIFS({Project Remaining Hours}, {Project Status}, OR(@cell = "In Progress"))
Thanks,
Amy
Best Answer
-
Hi @Amy Evans
You can use @cell = "value" or simply use a comma between the {range} and the "criteria". The comma assumes an =.
You would more likely want to use @cell when you want something other than =, such as <> (not equal to).
The bit that's not necessary is the OR function. You only have one value you're looking for, so you don't need to look for "this" or "that", does that make sense?
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Amy Evans
You can add in a criteria that says only Sum if the value is greater than 0. Try this:
=SUMIFS({Project Remaining Hours}, {Project Status}, "In Progress", {Project Remaining Hours}, > 0)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you! Your formula is much more clean than mine. I used @cell = "In Progress" but I guess that's not necessary?
-
Hi @Amy Evans
You can use @cell = "value" or simply use a comma between the {range} and the "criteria". The comma assumes an =.
You would more likely want to use @cell when you want something other than =, such as <> (not equal to).
The bit that's not necessary is the OR function. You only have one value you're looking for, so you don't need to look for "this" or "that", does that make sense?
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Yes, thank you! So much to learn. Thank you for your guidance.
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