#UNPARSEABLE error

BINXS74

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 😊

DKazatsky2

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

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.

Lucas Rayala

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

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

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

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

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

@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

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

@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

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

THANK YOU!!!!!!! 😊

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