Help with #Unparseable Formulas

Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with multiple selections.

My Formula:

=SUMIFS({Deliverables}, {Work Type} HAS (@cell, Reprint), {Fiscal Quarter}, HAS (@cell, "Q1"), {Fiscal Year}, HAS (@cell, "FY24"))

I am looking for the amount of Deliverables that are marked as Reprint in Q1 of FY24.

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

I don't know if the extra spaces between HAS and your open parens would cause an issue, but you are also missing quotes around Reprint.

=SUMIFS({Deliverables}, {Work Type}, HAS(@cell, "Reprint"), {Fiscal Quarter}, HAS(@cell, "Q1"), {Fiscal Year}, HAS(@cell, "FY24"))

All comments

Carson Penticuff

I don't know if the extra spaces between HAS and your open parens would cause an issue, but you are also missing quotes around Reprint.

=SUMIFS({Deliverables}, {Work Type}, HAS(@cell, "Reprint"), {Fiscal Quarter}, HAS(@cell, "Q1"), {Fiscal Year}, HAS(@cell, "FY24"))

jimhmeyers

Thank you so much @Carson Penticuff I guess I totally missed that as I was typing the formula.

If you have a moment to look at this other formula I have been working on. I am looking for the amount of Deliverables in Q1 FY24 if they are marked as New or Revision.

=SUMIFS({Deliverables}, {Fiscal Year}, HAS(@cell, "FY24"), {Fiscal Quarter}, HAS(@cell "Q1"), {Work Type}, HAS(@cell, "New", "Revision"))

Carson Penticuff

Try this... HAS cannot match against two values at once, you have to use OR to evaluate for both.

=SUMIFS({Deliverables]}, {Fiscal Year}, HAS(@cell, "FY24"), {Fiscal Quarter}, HAS(@cell "Q1"), {Work Type}, OR(HAS(@cell, "New"), HAS(@cell, "Revision")))

jimhmeyers

That does not see to work when I put it in the sheet, but knowing that I need to use OR I think that will give me a good jumping off point to try and solve for that!

Thank you so much for all of your help! 😀😁

Carson Penticuff

My bad... I had an extra bracket in there... try this:

=SUMIFS({Deliverables}, {Fiscal Year}, HAS(@cell, "FY24"), {Fiscal Quarter}, HAS(@cell "Q1"), {Work Type}, OR(HAS(@cell, "New"), HAS(@cell, "Revision")))

jimhmeyers

That doesn't seem to work either. I will continue to play with it and see what I can come up with.

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.