# SUMIFS #INCORRECT

Options
✭✭✭✭

I have a formula referencing an external sheet's 4 columns that works fine:

=SUMIFS({Name 2022 - Travel To Site}, {Name 2022 - Travel Site To Site}, {Name 2022 - Travel From Site}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Then I have another formula referencing the same external sheet, but one less column, which is returning an #INCORRECT ARGUMENT SET.

=SUMIFS({Name 2022 - OOH 1.5x}, {Name 2022 - OOH 2x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

I've tried playing around with brackets, SUM, SUMIF, etc. but nothing works except if I put SUM, it only calculates the first column. What am I missing?

• Employee
Options

A SUMIFS Function requires a {Range} to be listed, then the "Criteria" that you're looking for in that range.

Ex:

=SUMIFS({Column to Sum}, {First Column}, "First Criteria", {Second Column}, "Second Criteria")

What is it you're looking to do with the additional ranges added in? Are you looking to SUM the two different columns, but using the same criteria?

If so, you'll want to actually add (+) two SUMIFS together, like so:

=SUMIFS({Name 2022 - OOH 1.5x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({Name 2022 - OOH 2x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Does that make sense? If this doesn't work, it would be helpful to see screen captures, but please block out sensitive data.

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee
Options

No problem!

For your other formula, it's missing a closing bracket after the second SUMIFS. You'll need to close off each statement fully before adding the next, like so:

=SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula)

Try:

=SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

And then you can add the fourth one on:

=SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Total Hours}, {NAME 2022 - Area}, OR(@cell = "Travel"), {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee
Options

A SUMIFS Function requires a {Range} to be listed, then the "Criteria" that you're looking for in that range.

Ex:

=SUMIFS({Column to Sum}, {First Column}, "First Criteria", {Second Column}, "Second Criteria")

What is it you're looking to do with the additional ranges added in? Are you looking to SUM the two different columns, but using the same criteria?

If so, you'll want to actually add (+) two SUMIFS together, like so:

=SUMIFS({Name 2022 - OOH 1.5x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({Name 2022 - OOH 2x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Does that make sense? If this doesn't work, it would be helpful to see screen captures, but please block out sensitive data.

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

@Genevieve P. That worked fantastic!

Now I have a similar one to add 3 columns, which is currently saying #NESTED CRITERIA, and I also want to include the formula below so it is one instruction (if possible):

=SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)))

=SUMIFS({NAME 2022 - Total Hours}, {NAME 2022 - Area}, OR(@cell = "Travel"), {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

• Employee
Options

No problem!

For your other formula, it's missing a closing bracket after the second SUMIFS. You'll need to close off each statement fully before adding the next, like so:

=SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula)

Try:

=SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

And then you can add the fourth one on:

=SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Total Hours}, {NAME 2022 - Area}, OR(@cell = "Travel"), {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

Awesome, thank you so much for your help. You saved me hours trying to figure it out!

• Employee
Options

No problem! I'm glad that this worked for you 🙂