3 Conditions - SUMIF or SUMPRODUCT or...?

**Madrabbitwoman** · 01-06-2009, 11:09 AM

Hi all, apologies if this has already been covered, I've been searching this forum all day but can't find the answer to this particular query.

I have 4 columns, A B C and D. They are a mix of formats.

A= Name (text)
B= Date
C= Code (text)
D= Number

I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.

I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.

Thanks in advance

**clownfish** · 01-06-2009, 11:19 AM

I'm sure the gods of the array / SUMPRODUCT formulas on here can provide a better solution, but an ugly one would be this (in Col E)

cell E1:
=SUM(E2:E10000)

cell E2:
=IF(AND([condition1],[condition2],[condition3]),D2,"")

fill down E2 all the way and your total will appear in E1.

**DonkeyOte** · 01-06-2009, 11:25 AM

=SUMPRODUCT(--(A1:A100="Name"),--(MONTH(B1:B100)=1),--(C1:C100="Code"),D1:D100)

Obviously "Name",1,"Code" can all be cell references containing the criteria.

Adjust ranges to suit but note pre XL07 you can not use entire columns nor should you... try to keep ranges to a minimum (for performance).

**Madrabbitwoman** · 01-06-2009, 11:31 AM

Thanks Clownfish, I just gave that a go, it may be user error, but it's resulting in a #VALUE! error.

I have (you need to ignore the ABCD ref above, as the columns aren't in that order, I was trying to keep it simple):

=IF(AND(B6:B500=N504,C6:C500=MONTH(1),E6:E500=O505),D6:D500,"")

Where;

column B is the Name, and the name condition is in cell N504
column C is the Date, in the format MMM/YY, I'd like to be able to put this condition into a cel too, so the user can change the date cell and that month's results appear
column E is the Code, the code condition is in cell O505
column D is the number that I need to add up from

Once I have this sorted, I will need an almost identical formula to work out the average number, rather than the total number, from another column.

**Madrabbitwoman** · 01-06-2009, 11:35 AM

Thank you both - DonkeyOte that worked (posted my reply before seeing yours).

Now, could I just ask if it's a simple task to make that last part, where it adds up the cells that meet the 3 conditions, to average them instead?

And while I'm at it could you explain briefly what the -- signs do in the formula. Should I press enter, or shift/ctrl/enter after entering the formula, as I only discovered that type of formula this morning!

**DonkeyOte** · 01-06-2009, 11:37 AM

Just so you know, this:

MONTH(1)

will always be 1

MONTH returns the MONTH number of the date within parentheses... given dates are integers 1 = 1st Jan 1900 (unless using 1904 system - Mac) ... so:

MONTH(1) is like saying MONTH(DATE(1900,1,1)) given the month is always Jan MONTH will always return 1.

In context of Sumproduct or CSE Array use along lines of:

MONTH(date range)=1

**DonkeyOte** · 01-06-2009, 11:46 AM

OK well let's first switch the references for sake of clarity, so your Sumproduct is:

=SUMPRODUCT(--($B$6:$B$500=N504),--(MONTH($C$6:$C$500)=1),--($E$6:$E$500=O505),$D$6:$D$500)

I'm not sure if using multiple formulae so have not set any absolutes on the criteria references... (if you are sometimes a Pivot Table is a better alternative).

The above although an array formula does not require CSE.

To get the average of the above you would need to divide the result by a further Sumproduct -- the same as the above albeit without the final sum range at the end, eg this:

=SUMPRODUCT(--($B$6:$B$500=N504),--(MONTH($C$6:$C$500)=1),--($E$6:$E$500=O505))
Which is effectively a multiple condition COUNTIF.

So you end up with:

=SUMPRODUCT(--($B$6:$B$500=N504),--(MONTH($C$6:$C$500)=1),--($E$6:$E$500=O505),$D$6:$D$500)/SUMPRODUCT(--($B$6:$B$500=N504),--(MONTH($C$6:$C$500)=1),--($E$6:$E$500=O505))

:-(

The other alternative is to use the AVERAGE function but as a CSE Array:

=AVERAGE(IF(($B$6:$B$500=N504)*(MONTH($C$6:$C$500)=1)*($E$6:$E$500=O505),$D$6:$D$500))

Myself I would opt for the CSE here to avoid effectively duplicating the Sumproduct.

**Madrabbitwoman** · 01-06-2009, 11:48 AM

Thank you DonkeyOte, after digesting that for a minute, it makes perfect sense.

I think my brain's turned to porridge - it seems to be a side effect of trying to work these Excel Sheets out for a whole day :s

I'm now struggling to do the next cell, it's basically the same except I need an average of the cells that meet all 3 criteria. I have tried changing the last part from

...Code"),D1:D100)

to

...Code"),AVERAGE(D1:D100))

This results in the #VALUE! error.

Any ideas?

**DonkeyOte** · 01-06-2009, 11:53 AM

For now I'll ignore the prior post as we obviously crossed and I think my prior answer answers your last questions

To understand more about Sumproduct and coercion (-- double unary) have a read through Bob's page on the subject:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

It is in essence used to coerce a boolean result (TRUE/FALSE) to it's numerical equivalent (1/0 respectively) ... it is regarded as being the slightly quicker method in terms of coercion to multiplication which is the traditional Sumproduct approach eg SUMPRODUCT((range1=criteria)*(range2=criteria)*range3)) ... though the latter method (*) is still required in certain instances though in this instance none of these conditions apply hence use of --.

**Madrabbitwoman** · 01-06-2009, 12:06 PM

Sorry, it was an overlapped post again.

I see what you mean about duplicating the formula, each name will have about 28 calculations, and there are about 20 names so I'm going to have to be careful about getting too complicated. I may make some helper columns, but was hoping to avoid that if possible.

Just tried the formula as you suggested:

=AVERAGE(IF(($B$6:$B$500=N504)*(MONTH($C$6:$C$500)=1)*($E$6:$E$500=O505),$D$6:$D$500))

Resulted in a #VALUE error again, ran through it and the IF argument wasn't complete, so I added a [value is false] bit to the end, resulting in the following:

=AVERAGE(IF(($B$6:$B$500=N504)*(MONTH($C$6:$C$500)=1)*($E$6:$E$500=O505),$D$6:$D$500,""))

BUT, this still results in the #VALUE! error.

I'll go and swat up on the info on the link you kindly added

**DonkeyOte** · 01-06-2009, 12:11 PM

You need not add the FALSE statement so this should work:

=AVERAGE(IF(($B$6:$B$500=N504)*(MONTH($C$6:$C$500)=1)*($E$6:$E$500=O505),$D$6:$D$500))

Re: #VALUE!

Do you have any such entries in your range D6:D500 -- pls check.

If you have lots and lots of these to do -- use a Pivot Table -- the best aggregation tool available in XL, bar none.

**Madrabbitwoman** · 01-06-2009, 12:25 PM

Hmm, curious. Still getting #VALUE!, all the cells are ok although some are empty (it's a list, each row is complete but only a section of rows are filled in so far) - I'm trying to set this sheet up for our receptionist so ease-of-use is more important that ease-of-setting-up!

PivotTable - unfortunately not something I've ever dealt with, so I feel some homework coming on! That would probably be perfect for this purpose but I'm a bit limited on time and didn't think I'd be able to learn how to use them and perform Averages etc quickly enough.

Thanks for that link BTW, I've added that to my favourites as it really well written; I manages to get my head around double unaries!