Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel SUM formula

    Hi,

    Can anyone tell me how to the SUM formula can ignore #Value! within a range. Basically there are formulae within my range that wont always return a value so would like to ignore them when it occurs.

    Any assistance appreciated!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can use SUMIF:
    =SUMIF(somerange,"<>#VALUE!")
    or add some error handling so the formulas return 0 or "" instead of #VALUE!
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    You can use SUMIF:
    =SUMIF(somerange,"<>#VALUE!")
    or add some error handling so the formulas return 0 or "" instead of #VALUE!
    How do I get the IF formula to return 0 instead of "VALUE!"?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you have 2007 or later, you just use:
    =IFERROR(your current formula,0)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Its fine I have managed

  6. #6
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •