SUMIFS Formula

MichelleBohn

What am I missing...I'm trying to sum the 'Headcount' column only if the Name column = "OPEN" and the Title column = "Supervisor". I keep getting the #INCORRECT ARGUMENT SET error.

TIA

Kelly Moore

Hey @MichelleBohn

The range of all criteria must be the same length. You have the SUMIFS range beginning at row 2 and all other ranges beginning at row 1. If your criteria is correct, you should be able to call out the entire column without using any row numbers, assuming the words in the orange row do not match your criteria. Sometimes I add a helper checkbox column called Header so I can easily tag header rows to make sure they are excluded from calculations. Mostly, I utilize the Summary Fields (right panel menu) so I don't need Summary rows mixed in with the data.

Your screenshot confused me when I tried to find your criteria. The word SUPERVISOR does not appear in the TITLE column nor did OPEN appear in the Name column. Based on your screenshot, your result would be zero. What result were you expecting?

=SUMIFS(Headcount:Headcount, Name:Name, "OPEN", Title:Title, "Supervisor")

This is how the formula will look, assuming your criteria is correct and assuming I can use the entire column range. The advantage of not using specific row numbers is the formula range will dynamically grow if dataset grows

Kelly

Kelly Moore

Hey @MichelleBohn

The range of all criteria must be the same length. You have the SUMIFS range beginning at row 2 and all other ranges beginning at row 1. If your criteria is correct, you should be able to call out the entire column without using any row numbers, assuming the words in the orange row do not match your criteria. Sometimes I add a helper checkbox column called Header so I can easily tag header rows to make sure they are excluded from calculations. Mostly, I utilize the Summary Fields (right panel menu) so I don't need Summary rows mixed in with the data.

Your screenshot confused me when I tried to find your criteria. The word SUPERVISOR does not appear in the TITLE column nor did OPEN appear in the Name column. Based on your screenshot, your result would be zero. What result were you expecting?

=SUMIFS(Headcount:Headcount, Name:Name, "OPEN", Title:Title, "Supervisor")

This is how the formula will look, assuming your criteria is correct and assuming I can use the entire column range. The advantage of not using specific row numbers is the formula range will dynamically grow if dataset grows

Kelly

MichelleBohn

Thank you so so much @Kelly Moore , after all day staring at it, I just gave up. I was checking the end range number but I never noticed the beginning. THANK YOU THANK YOU!

I agree I should use Summary Fields but for now, I'm working with new employees in smartsheet and this was how their excel was set up so I may start here and then move over to the Summary panel.

Sorry about the screenshot I was trying to get the column names & zoning in on the fomula so your correct both OPEN & Supervisor weren't listed, but you fixed it anyway!