#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
-
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
-
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
-
Hi @BINXS74 -- you just missed a comma after {Qualification}. Looks good otherwise!
-
@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???
-
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
-
should this be =SUM({Class Year}, "2023", {Current Yr}, "Year 11", {Qualification}, "Cert II Business")
I get an #INVALID REF error using this code.
-
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.
-
@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
-
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.
-
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.
-
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
-
THANK YOU!!!!!!! 😊
I was on the right track, but you've helped me to work it all out. Your amazing.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives