software

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,quot;mquot;,sumproduct(a2:a5,B2:b5))
but didnot work
Any ideas
Thanks
Tina
Try
=SUMPRODUCT((A2:A5),(B2:B5)*(C2:C5=quot;lquot;))
=SUMPRODUCT((A2:A5),(B2:B5)*(C2:C5=quot;mquot;))

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=520062=SUMPRODUCT((C2:C5=quot;mquot;)*(B2:B5)*(A2:A5))
hit control shift enter (NOT enter only)

instead of quot;mquot; replace quot;lquot;

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

=SUM(IF(C2:C5=quot;mquot;,(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)

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