Thread: Sumproduct with Range Names

Hi,

I have been using range names for each row in my worksheet and now I am trying to use sumproduct but I can't get it to work using the range names as it takes the entire range, not just the current column. I have rangenamed by columns as well so I tried using those to define it but with no success...any help would be great, maybe a UDF could do it??

I use that quite often
but you must ensure that there is only one cell ( if that is what you need)
in the range available to use in the formula.
Ie.
ColumnA B C D
point1.x point1.y point1.z

Row1 is NamedRange: NOMINAL 1
Row2 is NamedRange: UPPER 0.5
Row3 is NamedRange: LOWER -0.5
Row4 is NamedRange: ABS ABS(F47)
Row5 is NamedRange: LSL SUM(NOMINAL-ABS)
Row6 is NamedRange: USL SUM(NOMINAL+F46)
Row7 is NamedRange: MIN MIN(F72:F542)
Row8 is NamedRange: MAX MAX(F72:F542)
Row9 is NamedRange: RANGE SUM(MAX-MIN)
Row10 is NamedRange: AVE AVERAGE(F72:F542)
Row11 is NamedRange: MEDIAN MEDIAN(F72:F542)
Row12 is NamedRange: AVE-NOM SUM(AVE-NOMINAL)
Row13 is NamedRange: Stdev STDEV(F72:F542)
Row14 is NamedRange:
CPK: IF(USL-AVE>AVE-LSL,(AVE-LSL)/(Stdev*3),(LSL-AVE)/(Stdev*3))


CPK would be in row14 in column B, C, D…etc and pull the Named Range from
that column ( Any Named Range occurs only once every column)
HTH


"soteman2005" wrote:

>
> Hi,
>
> I have been using range names for each row in my worksheet and now I am
> trying to use sumproduct but I can't get it to work using the range
> names as it takes the entire range, not just the current column. I
> have rangenamed by columns as well so I tried using those to define it
> but with no success...any help would be great, maybe a UDF could do
> it??
>
>
> --
> soteman2005
> ------------------------------------------------------------------------
> soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078
> View this thread: http://www.excelforum.com/showthread...hreadid=510528
>
>

Use the space as the intersection operator. Type the defined name, a space
and then click the column header. This will give you only the "current"
column.

--
Kind regards,

Niek Otten

"soteman2005" <soteman2005.22yona_1139487601.488@excelforum-nospam.com>
wrote in message
news:soteman2005.22yona_1139487601.488@excelforum-nospam.com...
>
> Hi,
>
> I have been using range names for each row in my worksheet and now I am
> trying to use sumproduct but I can't get it to work using the range
> names as it takes the entire range, not just the current column. I
> have rangenamed by columns as well so I tried using those to define it
> but with no success...any help would be great, maybe a UDF could do
> it??
>
>
> --
> soteman2005
> ------------------------------------------------------------------------
> soteman2005's Profile:
> http://www.excelforum.com/member.php...o&userid=29078
> View this thread: http://www.excelforum.com/showthread...hreadid=510528
>