Sumifs, referencing another sheet with two conditions
I am trying to add numbers based on 2 conditions. I am referencing another sheet. I am using the following formula:
=SUMIFS({FY21 AA AMER Budget US$}, {FY21 AA AMER Quarter}, contains "2021",[{FY21 AA AMER Marketing Cost Type}, contains "Content"])
I want to add the budget for AA AMER if the following two conditions are met:
1) Quarter column contains 2021 (@cell level) and
2) Marketing cost Type (@cell level) contains 'content'
I am getting an unparseable error message.
What's wrong with this formula?
Thank you for your help with this.
Best Answer

@FViret ,
99.9% of the time, my Unparseable errors happen due to parentheses, curly bracket and square bracket errors. In your formula above you're using the CONTAINS function to locate your criteria, but you aren't opening a parenthesis to execute the function with its variables or closing it.
I'm assuming from your description of the problem that the Quarter Column contains both a year and quarter designator (e.g., 2021 Q1) and your Marketing cost type will have more verbiage than just content designating many types of content. If that's a valid assumption, try this.
=SUMIFS({FY21 AA AMER Budget US$}, {FY21 AA AMER Quarter}, contains("2021",searchwithin),[{FY21 AA AMER Marketing Cost Type}, contains("Content",searchwithin)])
WHERE searchwithin defines the range where you're looking for your criteria.
I think there are also some displacements with curly and square brackets that you might need to tinker with also.
Curly brackets are used to contain offsheet reference ranges.
Square brackets are used to contain column headings that contain spaces or end in a numerical value.
In your last section of the SUMIF, you have both square and curly brackets attempting to contain the marketing cost type range and I think you can safely omit the square brackets if this is an offsheet reference.
Hope this helps.
Answers

@FViret ,
99.9% of the time, my Unparseable errors happen due to parentheses, curly bracket and square bracket errors. In your formula above you're using the CONTAINS function to locate your criteria, but you aren't opening a parenthesis to execute the function with its variables or closing it.
I'm assuming from your description of the problem that the Quarter Column contains both a year and quarter designator (e.g., 2021 Q1) and your Marketing cost type will have more verbiage than just content designating many types of content. If that's a valid assumption, try this.
=SUMIFS({FY21 AA AMER Budget US$}, {FY21 AA AMER Quarter}, contains("2021",searchwithin),[{FY21 AA AMER Marketing Cost Type}, contains("Content",searchwithin)])
WHERE searchwithin defines the range where you're looking for your criteria.
I think there are also some displacements with curly and square brackets that you might need to tinker with also.
Curly brackets are used to contain offsheet reference ranges.
Square brackets are used to contain column headings that contain spaces or end in a numerical value.
In your last section of the SUMIF, you have both square and curly brackets attempting to contain the marketing cost type range and I think you can safely omit the square brackets if this is an offsheet reference.
Hope this helps.

Malaina,
Thank you very much for your help with my query. I am very grateful. I read what you wrote and adapted the formula you gave to get the calculations I needed. I ended up using:
=SUMIFS({FY21 AA AMER Budget US$}, {FY21 AA AMER Quarter}, CONTAINS("2021", @cell), {FY21 AA AMER Marketing Cost Type}, CONTAINS("Content", @cell))
I am also assuming that an unlimited number of conditions could be added, using the same structure but I have not tried that yet. Something for me to do in the near future.
Thank you again and have a great day,
Florence
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!