Need help with nested formula to convert words to numbers in a row and then tally the result

Linden Jargons

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))

Linden Jargons

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

Linden Jargons

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