I need to sum all cells where the "deactivated" box is checked and the date is after 2/23/2022

Options

This is one of the formulas I've tried.

=SUMIFS({Annual Billing Amount}, {Deactivated?}, 1, {Date to Deactivate}, ">=2/23/2022")



Thanks for your help!

Tags:

Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Options

    Hi

    This should work for you

    =SUMIFS([Annual Billing Amount]:[Annual Billing Amount], Deactivated:Deactivated, @cell = 1, [Date to Deactivate]:[Date to Deactivate], @cell >= DATE(2022, 2, 23))

    IF the Annual Billing Amount and Deactivated are ranges, then just replace them with the range identifier.


    I hope that helps, please let me

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • deb_63_hydracor
    deb_63_hydracor ✭✭✭✭✭✭
    Options

    Thank you! Using the formula you provided, I changed the ranges as follows because I was referencing another sheet, however still getting an unparseable error.

    =SUMIFS([{Annual Billing Amountx}]:[{Annual Billing Amountx}], [{Deactivated?}:{Deactivated?}], @cell = 1, [{Date to Deactivate}]:[{Date to Deactivate}], @cell >= DATE(2022, 2, 23))

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Options

    Hi

    I am sorry, I have been and still am sick so my brain is foggy. However, the [{ and }] do not look like correct syntax:

    '[ ] are used when a col. has spaces always good to use them as best practice just in case. so [col 1]:[col1] is correct syntax to use a full col.

    '{ } are used when you reference a range so [Sheet Name Range Number} unless you rename the range which is also best practice. so {Name of the Range} is the correct syntax.

    See if you can fix the syntax and let me know if it's a range you only need the range and not {range}:{range}.

    I hope that helps and that I haven't misspoken.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!