sumif with multiple conditions and will need to divide total

LisaPetersonBW

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])

Genevieve P.

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