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
Check out the Formula Handbook template!