Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #VALUE! error in SUMIF formula

    I am trying to create a SUMIF formula using nonadjacent ranges and I am getting a #VALUE! error.
    =SUMIF((A3,C3,E3,G3,I3),"<>""",(A2,C2,E2,G2,I2))

    What is wrong? the formula works with adjacent ranges in the form A1:A5. I am trying to add a row of numbers if the cell in the reference row is not empty.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #VALUE! error in SUMIF formula

    If one of the cells contains text (eg blank space), then the sum() would fail because it only adds numbers.
    But also, SUMIF() seems to like a coherent range (A3:I3)rather than the intermediate ones
    Ruth

  3. #3
    ellisbill
    Guest

    Re: #VALUE! error in SUMIF formula

    If I understand what you need correctly, the following should work: =IF(AND(A3<>"",C3<>"",E3<>"",G3<>"",I3<>""),(A2+C2 +E2+G2+I2),"") or =IF(A3*C3*E3*G3*I3<>0,(A2+C2+E2+G2+I2),"")

Posting Permissions

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