UNPARSEABLE Error for Countif of overbudget tasks
Hello all!
I'm trying to calculate the number of the overbudget tasks in my sheet summary. I have a column that is Actual Cost, and another column that is Budget. The AI suggested formula was:
=COUNTIF([Actual Cost]:[Actual Cost], >[Budget]@row)
But when I hit enter, I receive the unparseable error. Could anyone help me understand what's wrong and how to fix this? Thanks 😊
Best Answer
-
You are going to need the helper column as previously mentioned to get this calculated on each row, but you can hide the helper column after setting it up so that it does not clutter your view.
Answers
-
Hello @TMS Sanaz G
I tested this out and it worked on my sheet:=COUNTIF([Actual Cost]:[Actual Cost], >Budget@row)
If this isn't working can you share a screen shot of your own?https://www.linkedin.com/in/zchrispalmer/
-
Hello @=Chris Palmer
I'm not trying this in the sheet itself. I'm trying it in the sheet summary as you can see below:
I tried adding some numbers just in case it's having difficulty parsing nothing, but that doesn't seem to be the issue.
-
@TMS Sanaz G Ahh AI formula strikes again…
I see what it did based on @=Chris Palmer demo.
Since you are using this for a sheet summary and you are trying to compare 2 values on a row, I don't think you can dynamically compare values from ranges like that in a formula.
You'll need to add an additional column (like a checkbox) to indicate that task is over budget, then you can use a countif in your summary field to count the number of rows that have the over budget box checked.
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Hi @Darren Mullen,
That's really interesting! I used a similar formula that compares that data in a range (due dates) with a static parameter (today's date) to find the number of overdue tasks, and it worked perfectly:
I also came across another community post that seems to be doing that and it worked for them. So, I just assumed that it would also work for comparing 2 values on the same row.
I would prefer not to clutter my grid view by adding too many columns, and then calculating the number from there.
-
@TMS Sanaz G Correct, you can compare to a static value
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
You are going to need the helper column as previously mentioned to get this calculated on each row, but you can hide the helper column after setting it up so that it does not clutter your view.
-
Thank you for your suggestions everyone.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!