Help with #Unparseable Formulas

Options

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.

Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    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"))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    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
    Carson Penticuff ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Carson Penticuff ✭✭✭✭✭✭
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!