SUMIFS formula is returning #UNPARSEABLE

I am trying to create a sheet summary that will sum my Count column when the corresponding rows have a Status of Closed and the Request Type is Product Update. (In other words, if the status of this line is "Closed" and the request type is "Product Update" then sum the count column for all rows that meet this criteria.

The Count column is Text/Number, Status is Drop Down (Single Select), Request Type is (Single Select)

Everything I have tried so far will return #UNPARSEABLE. I have confirmed that everything is spelled correctly and I believe I have all the proper syntax.

=SUMIFS(Count:Count, Status:Status, Closed, Request Type:Request Type, Product Update)

=SUMIFS(Count:Count, "Status:Status", Closed, Request Type:Request Type, "Product Update")

=SUMIFS(Count:Count, Status:Status, @cell =Closed, Request Type:Request Type, @cell= Product Update)

=SUMIF(Status:Status, "Closed", Count:Count) This works for me when I do not have to count based on the Request Type column but in this specific sheet I have two different Requests Types so I need a summary for "Product Update" and "Promotion Update".

Find more posts tagged with

Accepted answers

Alejandra

Hi @Shaun Spiller,

There are a couple of syntax errors in your formula. For example, string values like "Closed" and "Product Update", need to be enclosed in quotation marks. Also, column names with spaces or special characters need to be enclosed in square brackets.

Your formula should look like this:

=SUMIFS(Count:Count, Status:Status, "Closed", [Request Type]:[Request Type], "Product Update")

If needed, more information on the #UNPARESEABLE error message can be found in our help article: https://help.smartsheet.com/articles/2476176-formula-error-messages#unparseable

All comments

Alejandra

Hi @Shaun Spiller,

Your formula should look like this:

=SUMIFS(Count:Count, Status:Status, "Closed", [Request Type]:[Request Type], "Product Update")

If needed, more information on the #UNPARESEABLE error message can be found in our help article: https://help.smartsheet.com/articles/2476176-formula-error-messages#unparseable

Shaun Spiller

This worked perfectly.

Thank you. I tired with and without quotes around the string values but did not know "column names with spaces or special characters need to be enclosed in square brackets."

That additional details is greatly appreciate and enabled my calculations to run properly.

JH@HL

Sumifs forumula question here. For work allocation, each week team members report on the name of their project and the approximate percentage of time it will take. For example, Project A = 50% and Project B = 20%. I'm trying to sum the total of the projects. The first criteria is the status of the project; I want to count only "in progress" precentages. The second criteria is by contact. Since the sheet displays multiple team members, i want a cell to count only the percentages for that team member.

Two questions: first, i have been able to get the formula to work, but i didn't save it properly, and now i can't get back to that original formula. Second, usually when I do this kind of thing, I figure out the formula for the first team member, and then copy and paste the formula for all of the remaining team members, and simply change the contact name. However, on this sheet when i do that, when i make the copy, the original one then shows "unparseable"

Final note* this sheet is for a colleague in London so the word spelling for the word "utilisation" is different than it is spelled in the US. That's not the error.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.