# Calculated Column Exclude N/A from Sum

• ### Question

• I am creating a list which will track 12 columns with a score of 1-4 and N/A. I created the columns as a choice column (1,2,3,4,N/A). I need to create a calculated column which totals the points. However, if one column had an N/A I get the following #VALUE!.

Is there a formula to disregard any 'N/A' and provide the SUM.

Also, I will need a column to provide the average score while excluding the N/A column. (Example - there are 12 columns, 4 have an N/A. The formula would need to be able to divide the SUM by 8 columns, not 12)

Thank you for any assistance.

Friday, April 5, 2019 5:48 PM

• Hi movienut84,

Per my test in my SharePoint 2010 environment, firstly, I create four choice columns p1, p2, p3 and p4.

Then I create a calculated column called “sum” and add the formula as below.

=SUM(IF(p1<>"N/A",INT(p1),0),IF(p2<>"N/A",INT(p2),0),IF(p3<>"N/A",INT(p3),0),IF(p4<>"N/A",INT(p4),0))

Then I create another calculated column called “cal1” which provides the average scores excluding the N/A column. The formula as below:

=sum/COUNT(INT(p1),INT(p2),INT(p3),INT(p4))

The result as below:

For your issue, you should change the formulas above for the 12 columns.

Here is a detailed article for your reference:

Calculated Field Formulas.

https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v%3Doffice.14)

Best regards,

Sara Fan

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

Monday, April 8, 2019 9:29 AM

### All replies

• Hi

But if you want to display both sum and average, I am afraid you have to write your own logic code.

Justin Liu Office Apps & Services MVP, MCSE
Senior Software Engineer

Monday, April 8, 2019 8:33 AM
• Hi movienut84,

Per my test in my SharePoint 2010 environment, firstly, I create four choice columns p1, p2, p3 and p4.

Then I create a calculated column called “sum” and add the formula as below.

=SUM(IF(p1<>"N/A",INT(p1),0),IF(p2<>"N/A",INT(p2),0),IF(p3<>"N/A",INT(p3),0),IF(p4<>"N/A",INT(p4),0))

Then I create another calculated column called “cal1” which provides the average scores excluding the N/A column. The formula as below:

=sum/COUNT(INT(p1),INT(p2),INT(p3),INT(p4))

The result as below:

For your issue, you should change the formulas above for the 12 columns.

Here is a detailed article for your reference:

Calculated Field Formulas.

https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v%3Doffice.14)

Best regards,

Sara Fan

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

Monday, April 8, 2019 9:29 AM
• Hi movienut84,

Best regards,

Sara Fan

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

Thursday, April 11, 2019 9:55 AM
• Hi movienut84,

I am checking to see how things are going there on this issue. Please let us know if you would like further assistance.

If the issue was resolved, you can mark the helpful post as answer to help other community members find the helpful information quickly.

Best regards,

Sara Fan

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.