# calclulate a sum excluding NA results. SP10

I have 6 columns.

Each column has a `NA`, `1` or `0` entered.

I need `column7` to calculate a score (preferably as a percentage), however NA should not be included in the count.

EG

``````1,  1,  1,  0,  NA, 1  =  4/5

0,  0,  0,  1,  1,  1 = 3/6

NA,  NA,  1, 1, 1, 1 = 4/4
``````

Let's say, six columns are `C1`, `C2`, `C3`, `C4`, `C5`, `C6`. So now you can just check the value before adding and dividing. I mean following

``````=(
IF(C1="NA",0,C1) +
IF(C2="NA",0,C2) +
IF(C3="NA",0,C3) +
IF(C4="NA",0,C4) +
IF(C5="NA",0,C5) +
IF(C6="NA",0,C6)
)/
(
IF(C1="NA",0,1) +
IF(C2="NA",0,1) +
IF(C3="NA",0,1) +
IF(C4="NA",0,1) +
IF(C5="NA",0,1) +
IF(C6="NA",0,1)
)
``````

# Explanation

1. `IF(C1="NA",0,C1)` means It will add `0` if `C1` is `NA`. Otherwise it will add `C1` itself. So `NA` is no more in the sum.

2. `IF(C6="NA",0,1)` mean the count will not increase if the value is `NA`

# Output using question's Input If you need (A/B) type output, then you can use following formula:

``````=CONCATENATE(
(
IF(C1="NA",0,C1) +
IF(C2="NA",0,C2) +
IF(C3="NA",0,C3) +
IF(C4="NA",0,C4) +
IF(C5="NA",0,C5) +
IF(C6="NA",0,C6)
),
"/",
(
IF(C1="NA",0,1) +
IF(C2="NA",0,1) +
IF(C3="NA",0,1) +
IF(C4="NA",0,1) +
IF(C5="NA",0,1) +
IF(C6="NA",0,1)
)
)
``````

In that case output will be like following • Thanks for your reply. however I don't believe this worked in a calculated column in SP. – Blake Feb 4 '16 at 4:00
• Did you try?.... – Atish Dipongkor - MVP Feb 4 '16 at 4:09
• There was a extra plus sign (+). I have updated my answer – Atish Dipongkor - MVP Feb 4 '16 at 5:58
• Hello, This formula did not work in my calculated column. I have received this error:The formula contains a syntax error or is not supported. Maybe I need to create a Nintex workflow instead of a calculated column.. – Blake Feb 7 '16 at 22:17
• Did you change the column names properly? – Atish Dipongkor - MVP Feb 8 '16 at 3:16