Sum if by Status, Value & Month..

Jason P
Jason P ✭✭✭✭✭
edited 02/24/23 in Formulas and Functions

Hi All,

I have variables of the formula I want in other sheets but cannot get it to work when adding a third criteria. (Unparseable), have tried multiple connotations, reaching out for advice.

Basically I want the sum value for all confirmed projects completing in any given month. bold font below works on it's own returning a value

=SUMIFS(Status:Status, "Confirmed", [$CV Ex GST]:[$CV Ex GST]),[Completion date]:[Completion date] AND(IFERROR(MONTH(@cell), 0) = 3)

cheers.

Cheers.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jason P

    The SUMIFS function has the syntax =SUMIFS(range to be summed, range1, criteria 1, range 2, criteria2, etc). The formula above does not follow the expected syntax.

    =SUMIFS([$CV Ex GST]:[$CV Ex GST], Status:Status, "Confirmed", [Completion date]:[Completion date], IFERROR(MONTH(@cell), 0) = 3)

    Will this work for you?

    Kelly

  • Jason P
    Jason P ✭✭✭✭✭
    edited 02/26/23

    Morning @Kelly Moore

    Thank you, yes this works.

    I can see I need to study up on understanding of syntax's and how / what they look for. My =SUMIFS(Status:Status, "Confirmed", [$CV Ex GST]:[$CV Ex GST]) is working and I couldn't understand why adding the month criteria didn't work.. I have some COUNTIFS I'm working on as a side project in another sheet - will have "does not follow the expected syntax" in mind as I work through them.. Thank you again for your help.

    Cheers.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!