sumif and sumproduct together

[tina]

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

[Dav]

Tina

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

The last condition C2:C5="l" evaluates to a true of a false which when used in a formula becomes a 1 or a 0 (which is why it is multiplied by the previous expression (*) to make this happen. Obviously everything multiple by a 0 returns a 0 so it just sumproducts where it is true

Regards

Dav

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[R..VENKATARAMAN]

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

instead of "m" replace "l"

"tina" <[email protected]> wrote in message
news:[email protected]...
> 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
>