Results 1 to 10 of 10
Thread: SUMIF & AND

20010131, 01:25 #1
 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) ???

20010131, 07:48 #2
 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

20010131, 10:55 #3
 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.

20010131, 13:14 #4
 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 CtrlAltEnter 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

20010131, 14:02 #5
 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

20010131, 16:19 #6
 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.

20010131, 21:40 #7
 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.

20010201, 08:13 #8
 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

20010201, 09:47 #9
 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.

20010201, 13:32 #10
 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