Sumifs formula and isblank?

Stephanie Allison

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:

Danielle Arteaga

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!