SUMIFS criteria

Colin JanssenColin Janssen ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
03/20/18 Edited 12/09/19

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})


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.





Popular Tags:


  • Shaine GreenwoodShaine Greenwood Employee
    edited 03/22/18

    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:

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

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

  • Colin JanssenColin Janssen ✭✭✭✭✭

    Thanks Shaine. That solved my problem.

Sign In or Register to comment.