SUMIF based on 2 conditions

[Tim]

Hi,
I have data roughly laid out like this

Week Resource hours
1 res4 32
4 res2 3
2 res1 5
1 res2 31
2 res3 12
4 res4 10
and so on....
I need to to a sumif base on 2 conditions, something
like..
Sum the hours, for res 4, for week 1.
sum the hours for res 4 for week 2
etc etc
so its a SUMIF but I cannot get it to accept the 2
conditions - any ideas?
TIA
Tim

 

[Bob Phillips]

Hi Tim,

=SUM PRODUCT((A1:A100=1)*(B1:B100="res4"),(C1:C100))

=SUM PRODUCT((A1:A100=2)*(B1:B100="res4"),(C1:C100))

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harald Staff]

Hi Tim

This is a perfect job for a Pivot table:
http://www.cpearson.com/excel/pivots.htm

 

[Frank Kabel]

Hi
one way:
use a pivot table for this. Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
to get some starting information

 

[Stefan Hägglund [MSFT]]

Hi Tim,

You can use an array formula like the one below
The formula below must be entered as an array formula, press
CTRL+SHIFT+ENTER to enter the formula as an array formula..

If you have the datarange in A1:C7, then the formula can look like this:
=SUM(IF(A2:A7=1,IF(B2:B7="res4",C2:C7)))

You can read more about using SUM(IF()) array formulas in
http://support.microsoft.com/?id=275165


Stefan Hägglund
Microsoft