Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Help Please
"Joe Gieder" wrote:
It worked great thank you Good to hear that. .. what does L2:p2,,,7050 do? =SUMPRODUCT(($R$2:$R$7051=R2)*OFFSET($L$2:$P2,,,70 50)) $L$2:$P2 is the OFFSET's reference range (horiz ref range here). The 7050 is the height param to sync with the range size in col R ($R$2:$R$7051) which effectively "extends" the horiz ref range to cover all of L2:P7051 Alternatively we could also use: =SUMPRODUCT(($R$2:$R$7051=R2)*OFFSET($L$2:$L7051,, ,,5)) where $L$2:$L7051 is the OFFSET's vertical ref range, sized equal to $R$2:$R$7051, while the 5 is the width param to extend it to cover all of L2:P7051, as before. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Help Please
whoops ... think the OFFSET wasn't necessary here
This simpler version seems to work just as well: =SUMPRODUCT(($R$2:$R$7051=R2)*($L$2:$P$7051)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT help | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
Can I use SUMPRODUCT for this? | Excel Worksheet Functions | |||
how to use sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT vs CSE | Excel Worksheet Functions |