Adding Calculated Column to List to sum values in columns while ignoring blanks

Question

I have a questionnaire that populates a SharePoint list. There are a number of questions that return answers as picked from a choice column.

I have a calculated column that I'm trying to get to return a value as a result. There are 4 columns that can return a string value, the formula so far will interpret these values as a numerical value. The below formula works when none of the columns are blank.

Currently it returns a sum, but the objective is to return a percentage which ignores unanswered questions. So if Col1 is blank, it is assumed the question did not apply and should not affect the %, which means I can't use if([Col1]="High",3, if([Col1]="Med",2, if([Col1]="Low",1,0))) as a 0 value would negatively affect the percentage. I've tried a few variations of the below formula by adding in ISBLANK but these keep returning #Value! and #Name? for me.

Current Formula:

=SUM(
       if([Q1]="High",3, if(Q1]="Med",2, if([Q1]="Low",1)))
     , if([Q2]="High",3, if([Q2]="Med",2, if([Q2]="Low",1)))
     , if([Q3]="Often",3, if([Q3]="Monthly",2, if([Q3]="Never",1)))
     , if([Q4]="Yes",3, if([Q4]="Planned",2, if([Q4]="No",1)))
    )

What I would like to be able to do is divide the sum of these values by the number of questions that were not blank

Attempted formula (partial):

=SUM(IF(ISBLANK(Q1),"",1),IF(ISBLANK([Q2]),"",1))

The above returns 2 when Q1 and Q2 have answers, but when blank, it returns #Value? I assume because it is trying to add a number to a string.

Answer 1

Try using COUNT function which counts the number of arguments that contain numbers.

For example:

=COUNT(IF(ISBLANK([Q1]),"",1),IF(ISBLANK([Q2]),"",1))

For other available functions, refer documentation: Examples of common formulas in lists

Answer 2

Thanks @Ganesh Sanap

I was able to achieve the intended result using count as per below:

=(SUM(
       if(
            [Q1]="High",3, 
            if(
                [Q1]="Med",2,
                if(
                    [Q1]="Low",1,0
                    )
                )
            )
     , 
        if(
            [Q2]="High",3,
            if(
                [Q2]="Med",2,
                if(
                    [Q2]="Low",1,0
                    )
                )
            )
     , 
        if(
            [Q3]="Often",3,
            if(
                [Q3]="Monthly",2,
                if(
                    [Q3]="Never",1,0
                    )
                )
            )
     ,
        if(
            [Q4]="Yes",3,
            if(
                [Q4]="Planned",2,
                if(
                    [Q4]="No",1,0
                    )
                )
            )
    )
    -
    CountA(
        [Q1],
        [Q2],
        [Q3],
        [Q4]
    )
)
/
(
    CountA(
        [Q1],
        [Q2],
        [Q3],
        [Q4]
    )
*2
)