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

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.

• Try using COUNT function which counts the number of arguments that contain numbers. Let me know it solves your problem. Aug 24, 2022 at 16:34
• Thanks @GaneshSanap I was able to form a solution using CountA
– MH5
Aug 25, 2022 at 15:47

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

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