SUMIFS criteria headache

Claybowen
Claybowen ✭✭✭
edited 12/09/19 in Formulas and Functions

Hello all, 

Typically, building a formula through Smartsheet doesn't give me any issues but the SUMIFS formula specifically within Smartsheet is making me pull my hair out. 

I have 3 columns that I need to use in the formula. 

-Job Code 

-Needed Position Headcount

-Building Status

I need to add the total headcount of each job code if the building has a status of either "Communicated" or "TBA".

Currently, I have the attempted formula written as: 

=SUMIFS([Job Code]:[Job Code], [Needed Position Headcount]:[Needed Position Headcount], "FACBM1" [Building Status]:[Building Status], "Communicated" OR "TBA")

"FACBM1" is the specific job code I'm wanting to add based on the building being classified as with communicated or TBA.

Could someone point me in the right direction on how to properly configure this formula?

Thanks!

Tags:

Comments

  • JLC
    JLC ✭✭✭✭✭✭

    Think this should do it:

    =SUMIFS([Needed Position Headcount]:[Needed Position Headcount], [Job Code]:[Job Code], "FACBM1" [Building Status]:[Building Status], "Communicated" OR "TBA")

     

    You had the range to sum as the job code, and the criteria range as the headcount. Should be flipped!

  • Claybowen
    Claybowen ✭✭✭

    Thanks so much for your help in this! This formula seems to work fine with only one criteria at the end, such as "communicated". 

    When I try to place ...OR "TBA") the formula comes back unparseable.

    How can I add both criteria to the end of the formula?

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It's just the syntax for the OR. Give this a go...

     

    =SUMIFS([Needed Position Headcount]:[Needed Position Headcount], [Job Code]:[Job Code], "FACBM1" [Building Status]:[Building Status], OR(@cell = "Communicated", @cell = "TBA"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • JLC
    JLC ✭✭✭✭✭✭

    Ah! Sorry, going too quickly and didn't see that part. I don't have time to actually test this so hoping it's correct!! It will give you a good start if not. 

    =SUMIFS([Needed Position Headcount]:[Needed Position Headcount], [Job Code]:[Job Code], "FACBM1", [Building Status]:[Building Status], OR(@cell = "Communicated", @cell = "TBA"))

    Hope that helps!

  • Claybowen
    Claybowen ✭✭✭

    That was it!! Thanks so much for your help!

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!