Need help with nested formula to convert words to numbers in a row and then tally the result
I've created a sheet that's effectively a scorecard. Each column is a different area being measured. In one of the columns, I need to assign a value each time a particular word appears and tally the total score. It seems that what I'm missing is a reference to all of the columns in the section that needs to be tallied but not having any luck. What am I missing?
Here's the formula I have but I keep getting an 'UNPARSABLE'
=((COUNTIF("Excellent") * 4) + (COUNTIF("Satisfactory") * 3) + (COUNTIF("Unsatisfactory") * 2) + (COUNTIF("Not applicable") * 1)) / COUNT())
Tried this modified formula but still getting an unparsable result:
=((COUNTIF([Patient name, DOB Correct]@row:[Chart notes reviewed]@row "Excellent") * 4) + (COUNTIF([Patient name, DOB Correct]@row:[Chart notes reviewed]@row "Satisfactory") * 3) + (COUNTIF([Patient name, DOB Correct]@row:[Chart notes reviewed]@row "Unsatisfactory") * 2) + (COUNTIF([Patient name, DOB Correct]@row:[Chart notes reviewed]@row "Not applicable") * 1)) / COUNT([Patient name, DOB Correct]@row:[Chart notes reviewed]@row))
Best Answer

I was able to figure this out. I renamed the first and last columns to simplify the formula and used:
=COUNTIF(PatientDemographics@row:ChartNotes@row, "Excellent") * 4 + COUNTIF(PatientDemographics@row:ChartNotes@row, "Satisfactory") * 3 + COUNTIF(PatientDemographics@row:ChartNotes@row, "Unsatisfactory") * 2 + COUNTIF(PatientDemographics@row:ChartNotes@row, "Not Applicable") * 1
Seems to be working now
Answers

I was able to figure this out. I renamed the first and last columns to simplify the formula and used:
=COUNTIF(PatientDemographics@row:ChartNotes@row, "Excellent") * 4 + COUNTIF(PatientDemographics@row:ChartNotes@row, "Satisfactory") * 3 + COUNTIF(PatientDemographics@row:ChartNotes@row, "Unsatisfactory") * 2 + COUNTIF(PatientDemographics@row:ChartNotes@row, "Not Applicable") * 1
Seems to be working now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!