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
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
-
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 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!