sumif and sumproduct together

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try
=SUMPRODUCT((A2:A5),(B2:B5)*(C2:C5="l"))
=SUMPRODUCT((A2:A5),(B2:B5)*(C2:C5="m"))

Regards

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