Results 1 to 4 of 4

SUMIF based upon values in range of numbers

This is a discussion on SUMIF based upon values in range of numbers within the Excel Questions forums, part of the Question Forums category; Trying to create a sumif formula that will sumif if a range of data contains a real number. So if ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    Orlando, FL
    Posts
    111

    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
    61,981

    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

    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
    61,981

    Default Re: SUMIF based upon values in range of numbers

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

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