SUMIFS criteria

Colin Janssen

Hi All,

I'm trying to do a SUMIFS, and whilst I can return a result individually using SUMIF, when I try and combine I get #INVALID OPERATION.

The formula I am trying to use is

=SUMIFS({SERVICE DETAIL}, LEFT(@cell, 3) = "Col", {FEB 18}, {SERVICE TYPE}, "Transition", {FEB 18})

RESULT = #INVALID OPERATION

If I separate it out as below I get  a result

=SUMIF({SERVICE DETAIL}, LEFT(@cell, 3) = "Col", {FEB 18})

RESULT = $12,858.53

=SUMIF({SERVICE TYPE}, "Transition", {FEB 18})

RESULT = $45,856.12

I'm surely doing something stupid. Any help would be appreciated.

 

Regards

 

Colin

Shaine Greenwood

Hi Colin,

SUMIFS has a different syntax from SUMIF. With SUMIFS, you put the sum range first, then the criterion range, then criterion. More info on SUMIFS here: https://help.smartsheet.com/function/sumifs

I think what you're looking for is something like this:

=SUMIFS({FEB 18}, {SERVICE DETAIL}, LEFT(@cell, 3) = "Col", {SERVICE TYPE}, "Transition")

Colin Janssen

Thanks Shaine. That solved my problem.