sumif multiple conditions 2

[MaxEd]

Anyone know how to do multiple conditions using a sumif or if it's even possible?

 

[Loomah]

Hi
This is actually the first time I managed to use array formulas in anger but rather than trying to explain here I'll point you to where I got my guidance (other than when xlbo has tried to explain to me!!)

http://www.cpearson.com/excel/array.htm

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[ETID]

Don't forget about the database functions built into excel.


DSUM
DCOUNT
DMAX
DAaverage
etc...

these functions are built with multiple criteria in mind.

 

[KenWright]

Use SUMPRODUCT, eg with criteria in E1 and E2:-

=SUMPRODUCT((A1:A100=E1)*(B1:B100=E2)*(C1:C100))

will sum all values in C1:C100 where Cols A & B meet the criteria in the same row.


The formulas exploit the fact that Excel interprets TRUE as 1 and FALSE as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A&quot*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products

Regards
Ken...............

 

[shaggi]

Hi,
don't know if you have already been answered but here goes.....say you have the following table:
a b c
1 novels bob 2
2 romance jenny 5
3 romance jenny 2
4 sci fi frank 1

if you need the quantity of romance books by jenny, this should work

sum(if(a1:a4="romance",if(b1:b4="jenny",c1:c4)))

this is an array formula, so ctrl shift and enter as opposed to just enter when you input the formula

hope this helps
Shaggi

 

[MaxEd]

Thanks for all your tips!! I was thinking the SUMIF could do it but I've never figured it out so I thought someone else would be able to give me some tips on how. Also the SUM being used with multiple conditional statements is kind of slow. Any ideas?

 

[ETID]

Have you tried these built in functions?


DSUM
DCOUNT
DMAX
DAaverage
etc...

these functions are built with multiple criteria in mind.

 

[xlbo]

Shaggi - your formula doesn't need the IFs:
sum(if(a1:a4="romance",if(b1:b4="jenny",c1:c4)))
would be equivalent to:
sum((a1:a4="romance&quot*(b1:b4="jenny&quot*(c1:c4))
Entered with CTRL+SHIFT+ENTER
Ken's explanation is pretty much identical to how I try and explain array formulae. The only thing I'd mention is that I find an array formula to be a bit more flexible than sumproduct (which isn't entered as an array formula but does the same job) but in terms of a straight multiple criteria sum, you are better off using sumproduct as it doesn't take nearly as much calc power as array formulae.
It is also well worth looking at the database formulae (as suggested by ETID) although I find them a bit cumbersome because you need a criteria range - but that's just my opinion ;-)


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Loomah]

I concur with my learned colleague(??) xlbo!!

The array formula is what you would have got to from the link I posted. I was just about to post it with the explanation when - TA-DAH! it was there. This I have only learned in the last two months but I still don't fully understand array formulas!

As for the time they take this is obviously different from case to case depending on PC, apps open etc but the only time I found them slow was when I had to amend the formulas - to include another criteria or change the criteria. This was working with 16000-20000 rows of data with one formula for each of between 70-100 suppliers they would take a couple of seconds to recalculate.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Dunno 'bout Learned Loomah (ps pleeeease beat the pool to the champ league). In terms of calc time, 10 array formulae will not make the slightest difference. However, 100 or so may well so 10 lines with 10 columns of array formulae is probably enough to slow your recalc times down from 1 or 2 secs to 5-10 secs. I'm sure that system spec will have something to do with recalc time but I think it's more dependant on how many cells excel has to calc - and AFAIK, array formulae hit the calc tree pretty hard

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ETID]

Hmmmm...I can see my posts on database functions but they are invisible to the entire internet community...very strange.

 

[Zathras]

Don't feel too bad, ETID. Half of my posts are ignored too. For the record, I agree that database formulas are probably the best way to go when there are multiple conditions.

MaxEd, if you're still listening (I see you switched off "notify me&quot, you really ought to take a look at database techniques in the help file.

 

[ETID]

I was curious, sometimes I miss the point, and need to be smacked upside the head.


If speed of execution was a concern,...I would think that you would stay away from nested functions and formulas.


So, maybe I'd learn something by asking.

 

[xlbo]

ETID - I didn't ignore you:
"It is also well worth looking at the database formulae (as suggested by ETID) although I find them a bit cumbersome because you need a criteria range - but that's just my opinion"

I have to say that I used to use them but found that the whole criteria range thing was a bit too much like hard work for me and I do think that array formulae give a certain amount of flexibility that database formulae don't. Most times when I use arrays, it's actually quite a complex formulae and there aren't many of them. I do agree that if you have only standard multiple criteria (ie if x=1 and y =2 then sum c) there's no real point in using arrays - you should use db formulae or sumproduct

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ETID]

Thanks XLBO...

I guess I work in a non standard arena ... that is to say that alot of the workkbooks that I build are odbc linked to half million (+) record sources.

So even when queried it's a lot of data returned, and filling array formulas (of which you are one of the masters by the way) can bog down performance.

So I use Dfunctions when I can...and even though they are a bit cumbersome to setup, they are in most of my cases a "set it and forget it" type thing.

 

[xlbo]

Yup - I do use a lot of ODBC type queries but I generally only use the arrays to do the summaries on a master sheet and try and keep it to a few only - If I need more than a few array formulae, I alter the method or use code - they are very useful but certainly not the be all and end all of multiple criteria analysis

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[RobCPA]

I've often wished that you could use multiple criteria with the Sumif formula. Sumif can only take one criteria, and if you want to use more than one criteria (with the exception below), you'll need to use array formulas. John Walkenbach has a great explanation of both in his book Excel Formulas 2000 pages 200 - 206.

I've found a somewhat creative way, however, of using multiple criteria in a sumif formula, but is limited in that the match has to be exact, meaning you cannot use greater than or less than calculations. In the main database, take the multiple field elements and combine (concantinate) them in another field. Then, when you build the sumif statement, use the combined elements as the criteria.

Rob

 

[MaxEd]

ROBcpa,

Sounds like a great idea to just contatenate a bunch of TRUE or FALSE columns for what you want to accomplish. Thanks to everyone who has contributed to this discussion.