1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: SUM(IF (formula puzzle

Discussion in 'Business Applications' started by hnw, Feb 21, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. hnw

    hnw Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    9
    =SUM(IF(($N$16:$N$9500>0)*($J$16:$J$9500=""),$M$16:$M$9500-$G$16:$G$9500)) produces a numeric result

    =SUM(IF(($N$16:$N$9500>=0)*($J$16:$J$9500=""),$M$16:$M$9500-$G$16:$G$9500)) produces a #VALUE error.

    I am baffled. Using Excel X on a Mac.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    i get a #VALUE error for both
     
  3. hnw

    hnw Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    9
    I thought you were supposed to help me, not the other way around :)
    Only kidding. Still puzzled.
     
  4. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    I got the same as etaf, for what it's worth. Wonder if that forced '=0' is kicking out the error.

    =SUM(IF(($N$16:$N$9500>=0)
     
  5. hnw

    hnw Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    9
    Thanks. I do know that if I try => instead of >= Excel objects and makes the change, with the resulting error mentioned. Apparently the "=" is a problem. A bug??
     
  6. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    dunno if it's a bug or not; the pemdas stuff and excel has always bugged me.

    You are correct, however, in that it is a rather odd one. No worries, however, we've got a bunch of good MS office folk around, someone will solve it.

    Just out of curiousity, have you recreated it with Open Office or another spreadsheet app?
     
  7. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    and it gets more interesting. Here's what Excel had to say about the top formula and the error:
     

    Attached Files:

  8. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    Oo didn't help, got a #NAME? error........weirder and weirder.
     
  9. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    if you populate the formula fields with data, are you still getting the error?
     
  10. hnw

    hnw Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    9
    Absolutely. I'll try it with Numbers and report back. Good suggestion.
     
  11. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    thanks. I have meetings starting in about an hour, but I'll try to stick around.
     
  12. hnw

    hnw Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    9
    Tried Numbers but formulas don't transfer and if I copy/paste them they show errors with no explanations. The syntax is very different apparently. Don't know what else to do. I'll just stay puzzled. :)
     
  13. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    what version of office are you using?
     
  14. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    76,952
    never mind, just saw excel x on a mac......oooof. Not to strong on Mac stuff.......
     
  15. hnw

    hnw Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    9
    That's ok. The Mac/Excel gurus don't have an answer either. Fortunately I have workarounds that give me the answers I need. I appreciate the effort.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090441

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice