Sumif formula problem

Options
✭✭✭✭✭

Hi, I'm trying to modify this formula:

=SUMIFS({Total Sq Ft Scanned}, {Field Status}, "Completed", {Field Work Completed Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021))

The Field Status column is a drop-down. I want it to sum up every status EXCEPT completed (other stutuses are In progress, Scheduled, and on-hold), but I'm not sure how to re-write to do that?

• ✭✭✭✭✭
Options

Hey Kelly - you can rewrite it like this (as long as your existing formula is working):

=SUMIFS({Total Sq Ft Scanned}, {Field Status}, <>="Completed", {Field Work Completed Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021))

Basically, the "<>=" means "is not equal to".

Let me know if this solution works for you, and if it does, please mark it as such! Have a great day.

Love,

Brett Wyrick | Connect with me on LinkedIn.

------------------------------------------------------------------------------

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

• ✭✭✭✭✭
Options

Hi Brett, thank you! I just tried it, and I'm getting an error message. I realized I needed to change the date column to the date received (not date completed), which I did. However, it's still fighting me. I think it may be because some of the status fields are just blank.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!