Results 1 to 10 of 10

Thread: SUMIF & AND

  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF & AND

    how is it possible to combine the SUMIF & AND functions within excel?

    For example, how is it possible to return the sum of a given range, dependent on two different criteria and ranges?

    EG. SUMIF (Range1,Criteria1,Range2,Criteria2,sumrange) ???

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    Unfortunately, you can't have multiple criteria with the SUMIF function. However, you can accomplish the same thing using an array formula. In your example, if criteria1 is "=Value1" and criteria2 is "=Value2", then the following array formula will accomplish what you want:

    =SUM((Range1=Value1)*(Range2=Value2)*sumrange)

    Since that is an arrany formula, you must hold down the Shift+Ctrl keys when you press Enter to enter the formula into the cell.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    Hi,

    When I've had to do this based on simple criteria (rather than a range)- and when I've had to select on two different criteria based on the same line of source data- I've used a formula in another cell, and used my SUMIF based on that.

    For instance, I have a spreadsheet where column 2 can start with "A", "B" or "C"; and column 3 can start with "X" "Y" or "Z". Then instead of doing a COUNTIF (column 2 starts with "A" and column3 starts with "Y"), I create a new column 4- "=left(a2,1) & ":" & left(a3,1)". The SUMIF becomes (for instance) "=SUMIF(C1:C100,"=A:X",A1:A100)".

    HOwever, I've found that too many "SUMIFs" can lead to a really inefficient sheet- so when things are getting any more complex than that, I'm now inclined to go for VBA code. (When a worksheet recalc time was over 5 minutes, there was definitely a problem).

    Just some thoughts, which may well not be relevant.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Hemel and/or Luton, UK, Hertfordshire, England
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    I may be missing the point of the answers given to the inital question, but I have always used a different Array formula to solve this problem.

    If your idea is to do (using Lotus 123 syntax)...

    =SUMIF(a1:a10=5#AND#b1:b10<7,c1:c10)

    meaning check if a1=5 and b1<7 then add c1 THEN check if a2=5 and b2<7 then add c2 THEN etc etc... to row 10.

    then I find...

    {=SUM(IF(a1:a10=5,if(b1:b10<7,c1:c10)))}
    whereby the {} brackets are inserted by Excel when you press Ctrl-Alt-Enter to make the Array work.

    This works much quicker in large sheets than a standard SUMIF and has the ability to be easily changed to create new functions such as =AVERAGE(IF and =COUNT(IF

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    I gave the array formula :

    =SUM((Range1=Value1)*(Range2=Value2)*sumrange)

    To convert your formula to this technique would give this:

    =SUM((a1:a10=5)*(b1:b10<7)*c1:c10)

    The two are functionally the same, but the one above should be slightly faster. To see how this works, (a1:a10=5) will produce an array of 1's and/or 0's. A one where the corresponding cell is equal to 5 and a zero where it is not. The second logical statement will produce a one when the corresponding cell is less than 7 and a zero when it is equal to or greater than 7. When both statements are true, you end up with 1*1 which is equal to 1, and that one times the correspopnding cell from the from c1:c10 will add the value to the sum. If either or both logical statements is false, then the product is zero and zero is added to the sum. The multiply is just a little faster since it takes less code than an If. The time difference should be small and the answer should be the same.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Hemel and/or Luton, UK, Hertfordshire, England
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    Ah Ha! Thanks for your further explanation. I now see what you are doing and it certainly makes sense that it would be quicker your way.

    Cheers.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    Hey, that's nice.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    Glad you like it. You have my permission to use it any time you want. [img]/w3timages/icons/smile.gif[/img]
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    Leagre,

    I certainly will. And I suspect there will be a LOT of other people who come across your post who will also want to use it.

    I take it that's a blanket permission?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF & AND

    [pre]

    <font color=red>
    __________
    Permission___
    </font color=red>

    That's as close to permission under a blanket as I can get.
    [img]/w3timages/icons/laugh.gif[/img]
    Legare Coleman

Posting Permissions

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