none
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

Answers

  • Hi movienut84,

    We can achieve your requirement.

    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.

    SharePoint Server 2019 has been released, you can click here to download it.
    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
    Moderator

All replies

  • Hi

    Please refer below link.

    https://social.technet.microsoft.com/Forums/office/en-US/307f35da-3878-41b1-967a-08b46b1df3fd/sharepoint-list-view-group-sum-instead-of-count

    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
    Please Vote and Mark as Answer if it helps you.

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

    We can achieve your requirement.

    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.

    SharePoint Server 2019 has been released, you can click here to download it.
    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
    Moderator
  • Hi movienut84,

    If the reply is helpful to you, you could mark the reply as answer. Thanks for your understanding.

    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.

    SharePoint Server 2019 has been released, you can click here to download it.
    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
    Moderator
  • 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.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, April 15, 2019 2:19 AM
    Moderator
  • This worked perfect!! Thank you for the assistance.
    Wednesday, April 17, 2019 5:31 PM