SUMIFS #INCORRECT

Michelle Maas

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?

Genevieve P.

Hi @Michelle Maas

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

Genevieve P.

Hi @Michelle Maas

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

Genevieve P.

Hi @Michelle Maas

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

Michelle Maas

@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))

Genevieve P.

Hi @Michelle Maas

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

Michelle Maas

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

Genevieve P.

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