1. ## 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. ## 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.

3. ## 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.

4. ## 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. ## 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.

6. ## 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. ## Re: SUMIF & AND

Hey, that's nice.

8. ## Re: SUMIF & AND

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

9. ## 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?

10. ## 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]

#### Posting Permissions

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