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

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
Categories
Check out the Formula Handbook template!