Results 1 to 9 of 9

Thread: how to get the sum of a range in which two specific if conditions apply
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default how to get the sum of a range in which two specific if conditions apply

    i have so far =SUMIFS(a2:b18, a2:b18, a2 i want to get the sum in column a. in which b is greater than a. and a is greater than 30. example:
    a b
    10 11
    11 10
    12 10
    60 12
    100 150
    60 120


    in this exmaple the sum should returns 110
    Last edited by nadavrock; Jun 13th, 2019 at 05:51 PM.

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    wont let me edit. wannted to fix. what i have so far is =SUMIFS(a2:b18, a2:b18, a2

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,764
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    Quote Originally Posted by nadavrock View Post
    i have so far =SUMIFS(a2:b18, a2:b18, a2 i want to get the sum in column a. in which b is greater than a. and a is greater than 30. example:
    a b
    10 11
    11 10
    12 10
    60 12
    100 150
    60 120


    in this exmaple the sum should returns 110
    Why is the answer 110... shouldn't it be 100? The 10 you from the first line violates your "and a is greater than 30" rule, so why are you counting it?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,764
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    Quote Originally Posted by nadavrock View Post
    wont let me edit. wannted to fix. what i have so far is =SUMIFS(a2:b18, a2:b18, a2< b2)
    Put a space after your < sign, otherwise this forum's interpreter thinks you started and HTML tag.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,116
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    I think the result should be 160 - two last rows meet the criteria.

    M.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,116
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    Maybe

    =SUMPRODUCT(--(B2:B7>A2:A7),--(A2:A7>30),A2:A7)

    M.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,764
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    Quote Originally Posted by Marcelo Branco View Post
    I think the result should be 160 - two last rows meet the criteria.
    M.
    I missed that last one!!!



    Quote Originally Posted by Marcelo Branco View Post
    Maybe

    =SUMPRODUCT(--(B2:B7>A2:A7),--(A2:A7>30),A2:A7)
    Yeah, that is probably what the OP is after.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    May 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    yes marcelo that works. thank you. the idea of the example was indeed for the 2 last rows to meet cirteria. i miscalculated what sum that would give

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,116
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: how to get the sum of a range in which two specific if conditions apply

    You are welcome. Thanks for the feedback.

    M.

Some videos you may like

User Tag List

Tags for this Thread

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
  •