SUMIFS Multi Criteria Function
I'm trying to sum a total using two criteria and it won't work. I can do the sumif for each criteria seperately but when I try to do the "ifs" one to combine the criteria it says "unparseable".
I have these fields in one sheet and trying to sum them in a separate sheet:
Month/Year
Manager (as a Contact field but I don't think that will work so I added a code per manager)
Dollars (AP Written)
I want to add the dollars, by manager and by month/year so I enter this but it gives me the error. What am I doing wrong? What are my eyes not seeing that's incorrect here?
=SUMIFS({AP Written}, {DGA},CODE2,[{AP Written MONTH YEAR},Month2)
Summing only dollars by month works - =SUMIF({AP Written Tracked Range 1}, Month1, {AP Written Tracked Range 2})
Summing only by Manager Code works - =SUMIF({DGA}, CODE2, {AP Written})
Thank you!
Best Answer
-
Hi Megan,
Looks like all your column names are fine, but not lighting up. That, and the "unparseable" error indicate that there's likely a typo. It looks like there's a square bracket before the third range:
=SUMIFS({AP Written}, {DGA},CODE2,[{AP Written MONTH YEAR},Month2)
Take that out and you should be good to go!
=SUMIFS({AP Written}, {DGA}, CODE2, {AP Written MONTH YEAR}, Month2)
You may also want to adjust your the row references to use @row instead of the actual row numbers (in the case "2"):
=SUMIFS({AP Written}, {DGA}, CODE@row, {AP Written MONTH YEAR}, Month@row)
This will be applicable for any row that you copy/paste the formula into, and will help your sheet process faster as it doesn't need to search through the sheet to find the correct row number. (See here for more information about @row).
Let me know if this works for you.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Megan,
Looks like all your column names are fine, but not lighting up. That, and the "unparseable" error indicate that there's likely a typo. It looks like there's a square bracket before the third range:
=SUMIFS({AP Written}, {DGA},CODE2,[{AP Written MONTH YEAR},Month2)
Take that out and you should be good to go!
=SUMIFS({AP Written}, {DGA}, CODE2, {AP Written MONTH YEAR}, Month2)
You may also want to adjust your the row references to use @row instead of the actual row numbers (in the case "2"):
=SUMIFS({AP Written}, {DGA}, CODE@row, {AP Written MONTH YEAR}, Month@row)
This will be applicable for any row that you copy/paste the formula into, and will help your sheet process faster as it doesn't need to search through the sheet to find the correct row number. (See here for more information about @row).
Let me know if this works for you.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
THANK YOU! I was able to remove that bracket - AND use the @cell to specify the month. I have so much to learn! And this was one step in the right direction. =)
-
Wonderful! So glad I could help 🙂
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.8K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!