Can I use a named range in SUMPRODUCT?
 

I am trying to use a named range in SUMPRODUCT to shorten my formula. My
named range is called Data_Sel_WA. My formula is:
=SUMPRODUCT(--(Data_Selection!$D$7:$D$25=$A10),Data_Sel_WA)
My named range is defined as:
=Data_Selection!$F$7:$F$25,Data_Selection!$J$7:$J$ 25,Data_Selection!$N$7:$N$25,Data_Selection!$R$7:$ R$25,Data_Selection!$V$7:$V$25,Data_Selection!$Z$7 :$Z$25,Data_Selection!$AD$7:$AD$25
The formula is returning a #VALUE error. When the formula had the actual
cell ranges instead of the named range it worked. What's wrong?

Thanks,
Joe M.

Can I use a named range in SUMPRODUCT?
 

Don't think discontiguous ranges will work. Maybe just use simple links in an
empty area to the right to pull everything over and "make" it contiguous.
--
Max
Singapore
---
"Joe M." wrote:
I am trying to use a named range in SUMPRODUCT to shorten my formula. My
named range is called Data_Sel_WA. My formula is:
=SUMPRODUCT(--(Data_Selection!$D$7:$D$25=$A10),Data_Sel_WA)
My named range is defined as:
=Data_Selection!$F$7:$F$25,Data_Selection!$J$7:$J$ 25,Data_Selection!$N$7:$N$25,Data_Selection!$R$7:$ R$25,Data_Selection!$V$7:$V$25,Data_Selection!$Z$7 :$Z$25,Data_Selection!$AD$7:$AD$25
The formula is returning a #VALUE error. When the formula had the actual
cell ranges instead of the named range it worked. What's wrong?

Thanks,
Joe M.

Can I use a named range in SUMPRODUCT?
 

see your other post

--
Biff
Microsoft Excel MVP


"Joe M." wrote in message
...
I am trying to use a named range in SUMPRODUCT to shorten my formula. My
named range is called Data_Sel_WA. My formula is:
=SUMPRODUCT(--(Data_Selection!$D$7:$D$25=$A10),Data_Sel_WA)
My named range is defined as:
=Data_Selection!$F$7:$F$25,Data_Selection!$J$7:$J$ 25,Data_Selection!$N$7:$N$25,Data_Selection!$R$7:$ R$25,Data_Selection!$V$7:$V$25,Data_Selection!$Z$7 :$Z$25,Data_Selection!$AD$7:$AD$25
The formula is returning a #VALUE error. When the formula had the actual
cell ranges instead of the named range it worked. What's wrong?

Thanks,
Joe M.