none
VBA Sum Of Range RRS feed

  • Question

  • Is it possible to sum a range with a #N/A within it, Replace the #N/A's with "" or use an if statement to run code if it cant find the sum of a range because of the #N/A?

    Thursday, February 11, 2016 4:31 PM

Answers

  • Hi Soliddrew,

    To sum two values from the range, we can test whether the value from is a numric before caculate. Here is an demo for your reference to sum A1 and A2:

    If IsNumeric(Range("A1").Value) And IsNumeric(Range("A2").Value) Then
        Debug.Print Range("A1") + Range("A2")
    
    End If
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by André Santo Friday, February 12, 2016 12:34 PM
    • Marked as answer by Soliddrew Monday, February 15, 2016 8:54 AM
    Friday, February 12, 2016 5:35 AM
    Moderator

All replies

  • Hi Soliddrew,

    To sum two values from the range, we can test whether the value from is a numric before caculate. Here is an demo for your reference to sum A1 and A2:

    If IsNumeric(Range("A1").Value) And IsNumeric(Range("A2").Value) Then
        Debug.Print Range("A1") + Range("A2")
    
    End If
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by André Santo Friday, February 12, 2016 12:34 PM
    • Marked as answer by Soliddrew Monday, February 15, 2016 8:54 AM
    Friday, February 12, 2016 5:35 AM
    Moderator
  • Is it possible to sum a range with a #N/A within it, Replace the #N/A's with "" or use an if statement to run code if it cant find the sum of a range because of the #N/A?

    Have you tried =iserror(your formula),"") removing those pesky #N/As?

    Cheers

    Brad

    Sunday, February 14, 2016 2:35 PM