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.

Find more posts tagged with

Formulas

Accepted answers

Malaina Hudson

@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 off-sheet 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 off-sheet reference.

Hope this helps.

All comments

Malaina Hudson

@FViret ,

=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 off-sheet reference ranges.

Square brackets are used to contain column headings that contain spaces or end in a numerical value.

Hope this helps.

FViret

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.