We help IT Professionals succeed at work.

Using name ranges in sumproduct formula causing problems

avoorheis
avoorheis asked
on
I've noticed some strange behavior using named ranges in some sumproduct formulas. I was under the impression that you could replace a range of cells with a named range and that should work without a problem, but, it appears that's not always the case.
For example, this works fine:
=SUMPRODUCT((Final_OrderBookedDate>60)*(1=1))
where the named range is another worksheet in the same workbook.
this does not work, returns #VALUE!
=SUMPRODUCT((TODAY()-Final_OrderBookedDate>60)*(1=1))

likewise, this works
=SUMPRODUCT((Final_OrderBookedDate>60)*(Final_OppStage=100))
but, if i replace one of the named ranges with the actual range, it does not work....
=SUMPRODUCT((Final_OrderBookedDate>60)*(Final!$I$2:$I$1499=100))
it's returns #N/A

any ideas as to why named ranges are not as versital as I'm thinking they should be?
thanks
alan
Comment
Watch Question

Most Valuable Expert 2013
Commented:
SUMPRODUCT works OK with named ranges but there are rules to be followed....

For this one

=SUMPRODUCT((TODAY()-Final_OrderBookedDate>60)*(1=1))

If you get #VALUE! that's probably because the names range contains at least one text value, if you try to subtract a text value from a number (today) then you get #VALUE!, are you sure the named range doesn't contain headers or other text?

For this one

=SUMPRODUCT((Final_OrderBookedDate>60)*(Final!$I$2:$I$1499=100))

#N/A probably indicates that the 2 ranges are not the same size. Your named range needs to be a column with 1498 cells as that's the size of Final!$I$2:$I$1499

regards, barry
Most Valuable Expert 2013

Commented:
For a test try this formula

=ROWS(Final_OrderBookedDate)

that needs to return 1498 otherwise you get #N/A with the second problem formula as indicated above. How are you defining the named ranges?

regards, barry
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Hi Alan,

All formulas you listed worked for me in an example workbook I created.

The last formula may not work if the range shown (Final!$I$2:$I$1499) does not have the same size as the range for Final_OrderBookedDate.

Can you open up the attached workbook and see if it provides correct results for you?

 EE-sumproduct-test.xlsx

Author

Commented:
thanks guys...
Barry had it, I foolishly include the header row (row 1) in the range names.
However, there is still one formula that won't allow the use of the named ranges, returns the wrong number. I've checked all the range names, created with the name manager, and they are all set to scope = workbook. This formula works as is, but, when the ranges are replaced with the range names, i get the different numbers depending on which terms are replaced:
{=SUM(N(FREQUENCY(IF((Final!$S$2:$S$1499>L$36)*(Final!$S$2:$S$1499<M$36)*(Final!$B$2:$B$1499<>""),MATCH(Final!$B$2:$B$1499&"",Final!$B$2:$B$1499&"",0)),ROW(Final!$B$2:$B$1499)-ROW(Final!$B2)+1)>0))}
Most Valuable Expert 2013

Commented:
The part at the end is only the first cell of the range rather than the whole range, i.e.

ROW(Final!$B2)

what did you do with that when you used named ranges? If you want the named range in there then you need to use MIN too, i.e.

MIN(ROW(RangeName))

regards, barry

Author

Commented:
got it, thanks