Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which SumProduct Sumif or VLookup? | Excel Discussion (Misc queries) | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF or ... | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions |