How to incorporate an "is not checked" into a formula

Tricia Banks
Tricia Banks ✭✭✭✭
edited 08/06/23 in Formulas and Functions

Hi,

This formula is working great until I try and add in the section in which I want to only include revenue if the "Multi-year contract" column is unchecked. Help!

=SUMIFS([Contract Sales]:[Contract Sales], [Renewal Date]:[Renewal Date], >DATE(2024, 6, 29), [Renewal Date]:[Renewal Date], <DATE(2024, 10, 1), IF([Multi-year contract]1 = 0))

This part is causing the problem: IF([Multi-year contract]1 = 0))


Thanks!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Tricia Banks

    I think you are looking for this:

    SUMIFS([Contract Sales]:[Contract Sales], [Renewal Date]:[Renewal Date], >DATE(2024, 6, 29), [Renewal Date]:[Renewal Date], <DATE(2024, 10, 1), [Multi-year contract]:[Multi-year contract, 0)

    However if you are looking for the SUMIFS to only happen specifically when row 1 is unchecked, then the formula is

    =IF([Multi-year contract]1 = 0, SUMIFS([Contract Sales]:[Contracts Sales], [Renewal Date]:[Renewal Date], >DATE(2024, 6, 29), [Renewal Date]:[Renewal Date], <DATE(2024, 10, 1))

    Will either of these formulas work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Tricia Banks

    I think you are looking for this:

    SUMIFS([Contract Sales]:[Contract Sales], [Renewal Date]:[Renewal Date], >DATE(2024, 6, 29), [Renewal Date]:[Renewal Date], <DATE(2024, 10, 1), [Multi-year contract]:[Multi-year contract, 0)

    However if you are looking for the SUMIFS to only happen specifically when row 1 is unchecked, then the formula is

    =IF([Multi-year contract]1 = 0, SUMIFS([Contract Sales]:[Contracts Sales], [Renewal Date]:[Renewal Date], >DATE(2024, 6, 29), [Renewal Date]:[Renewal Date], <DATE(2024, 10, 1))

    Will either of these formulas work for you?

    Kelly

  • Tricia Banks
    Tricia Banks ✭✭✭✭

    The second one worked. Thanks so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!