Need help with a sumifs formula

I need to figure out why my "sumifs" formula is not working. I want to total a duration column if a payroll Item column contains one of 3 possible choices. My formula is as follows...


=SUMIFS(Duration:Duration, Payroll Item:Payroll Item, "Hourly",[Payroll Item:Payroll Item, "Overhead Hourly"], [Payroll Item:Payroll Item, "Officer Hourly"])


Not sure what is wrong with this formula or why it will not work.

Thanks!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @McAfee Electric Mgr

    There's a couple things to note here. The first is that whenever you have a column name with a space in it or numbers, you'll need to enclose the name in [these].

    For example:

    Payroll Item:Payroll Item

    Should be:

    [Payroll Item]:[Payroll Item]

    See: Create a Cell or Column Reference in a Formula


    The next thing is that since you're looking for multiple possible options, instead of listing them in a row like that (which means "AND", so it will exclude cells that only contain one option), we'll want to add in an OR statement and use @cell as the range in the OR statement, like so:

    [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell =  "Overhead Hourly", @cell = "Officer Hourly")


    Full formula:

    =SUMIFS(Duration:Duration, [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly"))


    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @McAfee Electric Mgr

    There's a couple things to note here. The first is that whenever you have a column name with a space in it or numbers, you'll need to enclose the name in [these].

    For example:

    Payroll Item:Payroll Item

    Should be:

    [Payroll Item]:[Payroll Item]

    See: Create a Cell or Column Reference in a Formula


    The next thing is that since you're looking for multiple possible options, instead of listing them in a row like that (which means "AND", so it will exclude cells that only contain one option), we'll want to add in an OR statement and use @cell as the range in the OR statement, like so:

    [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell =  "Overhead Hourly", @cell = "Officer Hourly")


    Full formula:

    =SUMIFS(Duration:Duration, [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly"))


    Let me know if this works for you!

    Cheers,

    Genevieve

  • Worked like a dream! Thank you!! 😊

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm so glad. Thanks for letting me know 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!