SUMIF based on 2 conditions

  • Thread starter Thread starter Tim
  • Start date Start date
T

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
 
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)
 
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
 
Back
Top