SUMIFs Formula Help...

Olivia Doiron

I am trying to calculate a specific persons total numbers across four columns in the Sheet Summary. Below is the formula that I have, but it keeps returning #Incorrect Argument Set. Please…what am I doing wrong. :)

=SUMIFS([Motion Permanent Total]:[Motion Permanent Total], [Motion Temporary Total]:[Motion Temporary Total], [Still Permanent Total]:[Still Permanent Total], [Still Temporary Total]:[Still Temporary Total], >0, [Media Manager]:[Media Manager], "David Creamer _DVIRC")

Janae G.

Hello!

SUMIFS can only sum values within a single column at a time. It can however choose the values to sum based on multiple criteria ranges (unlike SUMIF, which can only handle one criteria range). Looks like you will need a few SUMIFS in your formula that you will add together to find the total for David Creamer.

As you have in your formula, use SUMIFS if you only want values that are both >0 and belong to David Creamer:

=SUMIFS(range_to_sum, criteria_range1, criteria1, criteria_range2, criteria2…)

→You would have one of these for each column you want to sum- this column would go in range_to_sum.

→Here criteria range 1 is [Media Manager]:[Media Manager], criteria 1 is "David Creamer_DVIRC", criteria range 2 would be [Motion Permanent Total]:[Motion Permanent Total], and criteria is >0. Sometimes the range to sum and the criteria range are the same column, so you would type the same column range twice.

So your formula would look something like this:

=SUMIFS([Motion Permanent Total]:[Motion Permanent Total], [Motion Permanent Total]:[Motion Permanent Total], >0, [Media Manager]:[Media Manager], "David Creamer_DVIRC") + SUMIFS([Motion Temporary Total]:[Motion Temporary Total], [Motion Temporary Total]:[Motion Temporary Total], >0, [Media Manager]:[Media Manager], "David Creamer_DVIRC") …… plus SUMIFS for Still Permanent Total and Still Temporary Total

Here's a bit more on SUMIFS for reference: https://help.smartsheet.com/function/sumifs

Hope this helps!

Olivia Doiron

Thank you so much for the detailed answer! This helps my brain to logically understand how the SUMIFs formula calculates. :)