SUMIFS Formula

Enoch Mak

Hello, i'm trying to SUMIFS our total resources assigned by month and by two specific Occasions "Project Support" and "Commission". It comes out to Incorrect Argument Set confusing me where the formula maybe incorrect. Hoping to get another set of eyes on it.

Kelly Moore

Hey @Enoch Mak

Is your {Occasion} range a multiselect column, or a single select column? If single select, this would be an OR. If multiselect are you looking for either, or both simultaneously?

=SUMIFS({Total Resources Assigned}, MONTH([Column8]@row), {Occasion}, OR(@cell="Project Support", @cell="Commission"))

if multiselect:

=SUMIFS({Total Resources Assigned}, MONTH([Column8]@row), {Occasion}, OR(HAS(@cell,"Project Support"), HAS(@cell,"Commission")))

=SUMIFS({Total Resources Assigned}, MONTH([Column8]@row), {Occasion}, AND(HAS(@cell,"Project Support"), HAS(@cell,"Commission")))

Will any of these work for you?

Kelly

Enoch Mak

@Kelly Moore - the entire {Occasion} column is selected.

Tried all 3 formulas unfortunately i'm getting the Incorrect Argument Set error still.

I suspect the Month formula maybe bugged, since i took it out and i was able to sum by the Occasion

=SUMIFS({Total Resources Assigned}, {Occasion}, OR(@cell="Project Support", @cell="Commission")), so to keep troubleshooting.

I found that i needed to go a more roundabout solution. On my main sheet I had to return our Start Date column by the individual month turning it into a # (ie Jan = 1 and Feb = 2, etc...) in order to for my formula to reference it

I ended up creating a column called Month # Start Date

=SUMIFS({Total Resources Assigned}, {Occasion}, OR(@cell = "Commission", @cell = "Project Support"), {Month # Start Date}, "1")

Kelly Moore

@Enoch Mak

I see in your new formula above you have quotes around the number 'one'. Remove the quotes. Numbers, if you want them to behave as numbers, should never have quotes. Quote marks tell smartsheet to treat the value as a textstring.

Try this for the Month. You were previously missing a range for the month. If that isn't the correct column, choose the appropriate date column in your source sheet.

=SUMIFS({Total Resources Assigned}, {Month # Start Date},@cell=MONTH([Column8]@row), {Occasion}, OR(@cell="Project Support", @cell="Commission"))

Kelly