Sumif formula problem

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?

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    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.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭✭

    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