SUMIF based upon values in range of numbers

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: SUMIF based upon values in range of numbers

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    Orlando, FL
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIF based upon values in range of numbers

     
    Trying to create a sumif formula that will sumif if a range of data contains a real number. So if I have a range of five numbers like this:

    10
    20

    30
    10

    It should return the sum (70)

    It should also return a sum if one of my numbers is zero:

    0

    0

    0
    0

    Value = 0

    If a number is negative, it should also sum, but if no numbers are included in the range of values my answer should be blank.

    Thanks for your continued support! I don't know what I'd do without this board!

    Brian

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,447
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF based upon values in range of numbers

    This is confusing. Why do you mention SumIf while your examples can simply be covered with a Sum formula? The only thing I can gather is the wish to distinguish between a real 0 sum and a 0 sum as result of completely empty range.

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Location
    Orlando, FL
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF based upon values in range of numbers

    Quote Originally Posted by Aladin Akyurek
    This is confusing. Why do you mention SumIf while your examples can simply be covered with a Sum formula? The only thing I can gather is the wish to distinguish between a real 0 sum and a 0 sum as result of completely empty range.
    Aladin,

    You are exactly correct in what I trying to accomplish. For each line there are three possiblities:

    Dec Jan Feb Mar Apr May Jun Jul Result

    A set of real numbers:
    10 20 0 -10 20

    Another example of a set of real numbers
    0 -20 -20

    No numbers at all: (In this case the result should be blank)

    Keep in mind that numbers fall in random months so I would not want to reference one cell. This is why I'm thinking SUMIF might be my best option. If you or someone else might work be more effective, please let me know. I apologize for the confusion.

    Thanks,
    Brian

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,447
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF based upon values in range of numbers

      
    =IF(COUNT(A2:H2),SUM(A2:H2),"")

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com