#UNPARSEABLE error

Hi, Im new to smartsheet and trying to build a dashboard for our team.

We have school based students who Im building a dashboard for the program to reflect their progress.

Im currently trying to create on the dashboard a section that shows what qualification they are studying and the year in school that they are in


For example:

Year 11, Certificate II = number of participants

Year 11 Certificate III = number of participants

Year 12 Certificate II = number of participants

Year 12 Certificate III = number of participants

The code I'm trying to use to obtain this information is from two columns are as follows:

=SUM({Current Yr}, "Year 11", {Qualification} "Certificate II")

This is giving me #unparseable as an error.

Any assistance would be greatly appreciated.

Thank you 😊

Best Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @BINXS74,

    You are going to need to use =SUMIFS if you want to use a criteria based formula. Try this.

    =SUMIFS({Number of Participants}, {Current Yr}, "Year 11", {Qualification}, "Certificate II")

    {Number of Participants} is the range from the other sheet that you will be summing.

    Hope this helps,

    Dave

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    What I would suggest is adding two columns, one that includes the Year value you want to lookup for each line, and the second to include the Certificate value you want to look up (call these columns “Year” and “Certificate”). This will make those values much easier to track visually (they won’t be hidden in the formula), and it will allow you to use a single “column formula” instead of a hundred individual formulas. Once you’ve set up the two new columns, create a practice column to enter your column formula (don’t mess with your current work until you know you’ve got your new formula correct).

    The new formula will be:

    =SUMIFS({Number of Participants}, {Current Yr}, Year@row, {Qualification}, Certificate@row)

    Try this new formula on a single line to ensure it works.

    If it does, right click the cell with the formula and select Make Column Formula.

    This will populate every value for you. What you may notice is that where there isn’t a value in the Year or Certificate columns, you’ll get an odd value. If so, screen shot it and I’ll tell you what to do.

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @BINXS74 -- you just missed a comma after {Qualification}. Looks good otherwise!

  • BINXS74
    BINXS74 ✭✭

    @Lucas Rayala Thank you that has now given me a figure of 0 which is correct for some of them, but not all.



    This one should show 12 instead of 0


    Any suggestions???

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @BINXS74,

    You are going to need to use =SUMIFS if you want to use a criteria based formula. Try this.

    =SUMIFS({Number of Participants}, {Current Yr}, "Year 11", {Qualification}, "Certificate II")

    {Number of Participants} is the range from the other sheet that you will be summing.

    Hope this helps,

    Dave

  • BINXS74
    BINXS74 ✭✭
    edited 12/21/23

    should this be =SUM({Class Year}, "2023", {Current Yr}, "Year 11", {Qualification}, "Cert II Business")

    I get an #INVALID REF error using this code.

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @BINXS74 , my bad, Dave is correct, you’ll need a SUMIFS function. You can look up the explanation articles for SUM and SUMIFS in the Help and Leaning section. It looks like he provided the correct formula as well. You’re seeing zero values currently because the SUM function is incorrectly written and not returning any results. I’m going to give you some advice to make your life easier in a follow up response.

  • BINXS74
    BINXS74 ✭✭

    @DKazatsky2 and @Lucas Rayala thank you both for your help ☺️

    I've tried =SUMIFS({Number of Participants}, {Current Yr}, "Year 11", {Qualification}, "Cert III Business")

    still getting #INVALID REF

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    What I would suggest is adding two columns, one that includes the Year value you want to lookup for each line, and the second to include the Certificate value you want to look up (call these columns “Year” and “Certificate”). This will make those values much easier to track visually (they won’t be hidden in the formula), and it will allow you to use a single “column formula” instead of a hundred individual formulas. Once you’ve set up the two new columns, create a practice column to enter your column formula (don’t mess with your current work until you know you’ve got your new formula correct).

    The new formula will be:

    =SUMIFS({Number of Participants}, {Current Yr}, Year@row, {Qualification}, Certificate@row)

    Try this new formula on a single line to ensure it works.

    If it does, right click the cell with the formula and select Make Column Formula.

    This will populate every value for you. What you may notice is that where there isn’t a value in the Year or Certificate columns, you’ll get an odd value. If so, screen shot it and I’ll tell you what to do.

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    @BINXS74

    You are most likely getting the #INVALID REF due to incorrect cross sheet references. Make sure {Number of Participants}, {Current Yr}, and {Qualification} are all setup correctly.

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 12/21/23

    Ah, you’re not creating actual cross references for the values bracketed with the {}. Read this article:

    https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

  • BINXS74
    BINXS74 ✭✭

    THANK YOU!!!!!!! 😊

    I was on the right track, but you've helped me to work it all out. Your amazing.