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

Options
Linden Jargons
edited 09/28/22 in Formulas and Functions

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

  • Linden Jargons
    Answer ✓
    Options

    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

  • Linden Jargons
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!