sumif and sumproduct together
 

Hi
I am using sumproduct to calculate target units produced (range of std rates
multiplied by range of actual hours) what I would like to do is only sum if
shift is m(morning) or shift is l (late)
example
row col A col B col C
1 std rate act Hrs shift
2 1250 4.5 m
3 1500 3.75 m
4 1000 6.0 l
5 2500 3 m
there are over 40 rows and I cannot sort by shift as sorted by workcentre
I tried =sumif(a:c,"m",sumproduct(a2:a5,B2:b5))
but didnot work
Any ideas
Thanks
Tina

sumif and sumproduct together
 

Try
=SUMPRODUCT((A2:A5),(B2:B5)*(C2:C5="l"))
=SUMPRODUCT((A2:A5),(B2:B5)*(C2:C5="m"))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=520062

sumif and sumproduct together
 

=SUMPRODUCT((C2:C5="m")*(B2:B5)*(A2:A5))
hit control+shift+enter (NOT enter only)

instead of "m" replace "l"

"tina" wrote in message
...
Hi
I am using sumproduct to calculate target units produced (range of std
rates
multiplied by range of actual hours) what I would like to do is only sum
if
shift is m(morning) or shift is l (late)
example
row col A col B col C
1 std rate act Hrs shift
2 1250 4.5 m
3 1500 3.75 m
4 1000 6.0 l
5 2500 3 m
there are over 40 rows and I cannot sort by shift as sorted by workcentre
I tried =sumif(a:c,"m",sumproduct(a2:a5,B2:b5))
but didnot work
Any ideas
Thanks
Tina

sumif and sumproduct together
 

Not advocating this, but along your thought lines

=SUM(IF(C2:C5="m",(A2:A5)*(B2:B5)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"tina" wrote in message
...
Hi
I am using sumproduct to calculate target units produced (range of std
rates
multiplied by range of actual hours) what I would like to do is only sum
if
shift is m(morning) or shift is l (late)
example
row col A col B col C
1 std rate act Hrs shift
2 1250 4.5 m
3 1500 3.75 m
4 1000 6.0 l
5 2500 3 m
there are over 40 rows and I cannot sort by shift as sorted by workcentre
I tried =sumif(a:c,"m",sumproduct(a2:a5,B2:b5))
but didnot work
Any ideas
Thanks
Tina