SumIFs with Multiple Conditions and Reference Sheet

Options

Hello,

I have two sheets. On Sheet 1, I'm trying to write a statement that looks up all occurrences of each team member, like John Smith, on Sheet 1 and sums up the story points by month using the Resolved Column. See Jan-Pt summary. Each sheet only shows the first line. Sheet 1 will have many team members and month by month count. Sheet 2 is my data source referenced sheet with multiple lines for each team member and various dates.

Here's my attempt for the statement on Sheet 1 where it shows an "Incorrect Argument Set" error:

=SUMIFS({Name}, "[Team Members]@row", {Story Points}, {Resolved}, IFERROR(MONTH(@cell), 13) = 1)

Sheet 1:

Sheet 2 - Reference Sheet:


Thanks,

Amy

Best Answer

Answers

  • Werner Gerstacker
    Werner Gerstacker ✭✭✭✭✭
    Options

    SUMIFS are different from SUMIF in their syntax.

    For SUMIFS you need to have the column you want to sum up in the first place after the '(' followed by the criterion_range1 and the criterion1 for this range - and then you enter some more criteria.


    In your case the formula needs to start like this:

    =SUMIFS({Story Points}, {Name}, "[Team Members]@row", .....)

    I'm not sure, though, if your 2nd criterion - {Resolved}, IFERROR(MONTH(@cell), 13) = 1 - will actually work because your are trying to compare a date in the {Resolved} reference area with a number (maybe it even results in a TRUE/FALSE Statement) that I'm not sure the tool can resolve to begin with because I'm not sure what 'IFERROR(MONTH(@cell), 13) = 1' is trying to achieve.

  • Amy Evans2
    Options

    Thank you! This worked. Appreciate the quick response and helping me learn about the syntax! I'm a beginner and appreciate you explaining this to me so I can get better at these formulas.

  • Werner Gerstacker
    Werner Gerstacker ✭✭✭✭✭
    Options

    Thanks @Paul Newcome for the detailed explanation - haven't had the need to work with the MONTH(@cell) or YEAR(@cell) function yet, but it's good to know how it works 👍

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Werner Gerstacker I use it A LOT, so I am more than happy to share my knowledge. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!