Sumifs formula and isblank?
I'm likely missing something simple. I need a sum where the referenced column is either not "No" or if it is blank. The not "no" is easy, it works fine. It's incorporating the isblank that is kicking me in the behind.
=SUMIFS({FY24 Travel Plan - Department Master Total Cost}, {FY24 Travel Plan - Department Master Range ARU}, Appropriation@row, {FY24 Travel Plan - Department Master COMM Appprova}, <>"No")
When I try this formula, I receive an invalid data error.
=SUMIFS({FY24 Travel Plan - Department Master Total Cost}, {FY24 Travel Plan - Department Master Range ARU}, Appropriation@row, OR({FY24 Travel Plan - Department Master Range 2}, <>"No", {FY24 Travel Plan - Department Master COMM Approval}, ""))
Formula is in the Planned Travel Total Column.
And this is the referenced sheet:
Answers
-
I think you're running into difficulty because the [Comm Approval] range is a symbol column, which Smartsheet doesn't properly read as "blank." You would benefit from a "helper" column that translates that symbol into useable data. For example, you could add a column to the Department Master sheet next to [Commissioner Approval] and call it [Comm Approval Help]. In that column, place this column-level formula:
=IF([Commissioner Approval]@row="Yes", 0, 1)
You can then "hide" this column from the sheet.
Now, in your formula above, redefine the range to point to the helper column and change your "" at the end to 1.
=SUMIFS({FY24 Travel Plan - Department Master Total Cost}, {FY24 Travel Plan - Department Master Range ARU}, Appropriation@row, OR({FY24 Travel Plan - Department Master Range 2}, <>"No", {FY24 Travel Plan - Department Master COMM Approval Help}, 1))
(I don't think you need quotation marks around your 1 here, but you can always try it with/without.)
Hope this is helpful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 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!