SUMIFS Formula with Three Conditions

Options

Hello all! I'm fairly new to SmartSheets and having troubles with a formula for our Sheet Summary.

Here is a screenshot with what I have started. I want to calculate the total number of units if the employee equals "MB" and HSW is one of the Applicable Categories from the drop-down.

I used the formula below but it returned a value of 0. What part of my formula is currently wrong?

To make matters worse, I want to eventually add a third condition if the Date Earned column is in the year of 2023.

=SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], "HSW")


Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @twallen,

    Just add a

    HAS() to your formula and you will be all set - this is needed for the multi-select field.

    =SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"))

    Later, to add the year part, use this.

    =SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"), [Date Earned]:[Date Earned], @cell <> "", [Date Earned]:[Date Earned], YEAR(@twallen

    Hope this helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @twallen,

    Just add a

    HAS() to your formula and you will be all set - this is needed for the multi-select field.

    =SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"))

    Later, to add the year part, use this.

    =SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"), [Date Earned]:[Date Earned], @cell <> "", [Date Earned]:[Date Earned], YEAR(@twallen

    Hope this helps,

    Dave

  • twallen
    twallen ✭✭
    Options

    Hi @DKazatsky2. This was extermely the helpful! The first formula works just as intended so I marked it as answered.

    Can you please help me wrap up the second formula? I added 2023 to specify that year but I receive an #Invalid Data Type

    =SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"), [Date Earned]:[Date Earned], @cell <> "", [Date Earned]:[Date Earned], YEAR(2023))

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    @twallen,

    Sorry, not sure what happened with the formula last time. Here it is.

    =SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"), [Date Earned]:[Date Earned], @cell <> "", [Date Earned]:[Date Earned], YEAR(@cell) = 2023)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!