SUMIF for two conditions?

[Jake]

Hi,
I need to sum a range of values checking for two condtions. I check a shift
and week number and based on both conditions sum the total hours. I have
tried using the AND function without success. I thought it might be
possible to join two SUMIF functions, one checking for week number the other
for shift. I can't get it to work.
Any help much appreciated.
Thanks,
Jake

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A2:A200=E2),--(B2:B200=F2),C2:C200)

where A2:A200 is the shift values, B2:B200 is the weeknumbers,
and C2:C200 the hours you are totaling. E2 is the shift condition and F2
the weeknumber condition. Note that if your hours use time values like 08:00
for 8 hours
the result cell needs to be formatted custom as [hh]:mm to roll over 24
hours..
And if you need to make calculations with the total hours like for a
payrate
you need to multiply them with 24 as well

=Tot*24*payrate