sumif with multiple conditions and will need to divide total

Hi

In need of some help.

I would like for the formula to sum and then divide a total if two conditions are met

condition 1: must be a yes using the green dot with a check mark symbol

Condition 2: must be equal to CV PIP AC

Then the formula should total up and divide by 5 the values in "Total Contract Hours" column that meet those conditions.

I started the formula below. Any guidance is truly appreciated. Thank you

=SUMIFS(STATUS:STATUS, "Yes", PIP TYPE:PIP TYPE, = CV PIP AC, [TOTAL CONTRACT HOURS]:[TOTAL CONTRACT HOURS])

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @LisaPetersonBW

    You're very close! 🙂

    SUMIFS (plural) wants the range to SUM at the beginning of the function instead of the end:

    =SUMIFS([TOTAL CONTRACT HOURS]:[TOTAL CONTRACT HOURS],

    Then you can list each column and criteria after it. Keep in mind that columns with spaces need to have [square brackets] around their name, and criteria need "quotes" if you're looking for text or a symbol.

    Try:

    =SUMIFS([TOTAL CONTRACT HOURS]:[TOTAL CONTRACT HOURS], STATUS:STATUS, "Yes", [PIP TYPE]:[PIP TYPE], "CV PIP AC")

    Then you can divide the entire formula by 5 (once you're satisfied that the SUM is what you want to see):

    =SUMIFS([TOTAL CONTRACT HOURS]:[TOTAL CONTRACT HOURS], STATUS:STATUS, "Yes", [PIP TYPE]:[PIP TYPE], "CV PIP AC") / 5


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!