Sum if by Status, Value & Month..

Options
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.

Forever forwards Backwards never.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    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.

    Forever forwards Backwards never.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!