SumIFs with Multiple Conditions and Reference Sheet

Amy Evans2

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

Paul Newcome

@Werner Gerstacker

IFERROR(MONTH(@cell), 13) = 1

Basically you are looking at your date range and pulling the months.

IFERROR(MONTH(@cell), 13) = 1

But if there is a blank or non-date type value within the range, it will throw an error, so you use the IFERROR to replace errors with the number 13. 13 is used (I personally use zero but it serves the same purpose) because there is no month number 13. This means whichever row is throwing the error will not get included in any count for any month since you are not going to be counting anything for a month number that doesn't exist.

IFERROR(MONTH(@cell), 13) = 1

Then finally the "= 1" portion finishes out the criteria saying that if the month number equals 1, count it.

IFERROR(MONTH(@cell), 13) = 1

The same can be used to look at years as well.

IFERROR(YEAR(@cell), 0) = 2020

Werner Gerstacker

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

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.

Paul Newcome

@Werner Gerstacker

IFERROR(MONTH(@cell), 13) = 1

Basically you are looking at your date range and pulling the months.

IFERROR(MONTH(@cell), 13) = 1

But if there is a blank or non-date type value within the range, it will throw an error, so you use the IFERROR to replace errors with the number 13. 13 is used (I personally use zero but it serves the same purpose) because there is no month number 13. This means whichever row is throwing the error will not get included in any count for any month since you are not going to be counting anything for a month number that doesn't exist.

IFERROR(MONTH(@cell), 13) = 1

Then finally the "= 1" portion finishes out the criteria saying that if the month number equals 1, count it.

IFERROR(MONTH(@cell), 13) = 1

The same can be used to look at years as well.

IFERROR(YEAR(@cell), 0) = 2020

Werner Gerstacker

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

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