SUMIFS #INCORRECT
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?
Best Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Awesome, thank you so much for your help. You saved me hours trying to figure it out!
-
No problem! I'm glad that this worked for you 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!