Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Sumif formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How do I get a sumif formula or something like it to work for the following scenario

    Here is my data in column a and b

    1 100
    2 50
    3 70
    4 79
    5 85
    6 60
    7 78
    8 90
    9 100

    I want to add anything greater then 3 and less than 8 by looking at column "a" then adding the numbers in column "B"....the result would be 302 (79+85+60+78)

    Help?

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 14:23, lars wrote:
    How do I get a sumif formula or something like it to work for the following scenario

    Here is my data in column a and b

    1 100
    2 50
    3 70
    4 79
    5 85
    6 60
    7 78
    8 90
    9 100

    I want to add anything greater then 3 and less than 8 by looking at column "a" then adding the numbers in column "B"....the result would be 302 (79+85+60+78)

    Help?
    Try this:

    =SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))

    This sums all the numbers in column B that are greater than 3, then subtracts the sum of all numbers greater than or equal to 8, giving you 4-7.

    [ This Message was edited by: Russell Hauf on 2002-03-28 14:27 ]

  3. #3
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 14:23, lars wrote:
    How do I get a sumif formula or something like it to work for the following scenario

    Here is my data in column a and b

    1 100
    2 50
    3 70
    4 79
    5 85
    6 60
    7 78
    8 90
    9 100

    I want to add anything greater then 3 and less than 8 by looking at column "a" then adding the numbers in column "B"....the result would be 302 (79+85+60+78)

    Help?
    If you enter this formula as an array formula (instead of ENTER, press CTRL+SHIFT+ENTER) you will get the desired result.

    =SUM((A1:A9>3)*(A1:A9<8)*(B1:B9))

    Note, this will slow down spreadsheet calculations if you are evaluating a lot of data.

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    =SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))

    This sums all the numbers in column B that are greater than 3, then subtracts the sum of all numbers greater than or equal to 8, giving you 4-7.
    Much better answer than my Array Formula! Thanks Russell.

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One more:
    =SUMIF(A1:A8,">"&3,B1:B8)-SUMIF(A1:A8,">="&8,B1:B8)

    good luck

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,649
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default



    =SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))


    Much better answer than my Array Formula!

    No, Barrie. They are exactly equivalent in behavior and, I think also qua cost. The difference is that you don't have to worry about control+shift+enter at entering and at editing with the SUMPRODUCT version.

    Besides, Russell had better formulated it as:

    =SUMPRODUCT((A1:A9>3)*(A1:A9<8)*(B1:B9))

    or

    =SUMPRODUCT((A1:A9>3)*(A1:A9<8),(B1:B9))

    He probably had SUMIF in mind who knows .(See IML's post for the SUMIF version, which I'd prefer in cases of multiconditional summing with "between".

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-03-28 14:46 ]

  7. #7
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 14:45, Aladin Akyurek wrote:


    =SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))


    Much better answer than my Array Formula!

    No, Barrie. They are exactly equivalent in behavior and, I think also qua cost. The difference is that you don't have to worry about control+shift+enter at entering and at editing with the SUMPRODUCT version.
    kyurek on 2002-03-28 14:46 ]
    Thanks for clarifying that Aladin.

    Best regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Some videos you may like

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
  •