Dynamic Range and Array Formula (Excel 2003 SP2)

Topic

New Reply

I am attempting to perform Array math on a dynamic range
My range named count is =OFFSET(Data!$A$2,0,1,COUNT(Data!C:C),1)
My range named nme is =OFFSET(Data!$A$2,0,2,COUNT(Data!C:C),1)

I want to compare every value in nme to a constant and then multipy the result times the count, and then summing the whole thing.

{=sum((“BOB” = NME)*cnt)}

This works fine using a standard named range, but not if I use a dynamic range as above.
Did I miss something or is that just the way it is? Thanks.

Reply | Quote

Replies

As you indicate, the formula should be an array formula. Are you sure you confirmed it with Ctrl+Shift+Enter? It works OK for me. You can also use

=SUMPRODUCT((“Bob”=nme)*cnt)

as a normal (non-array) formula.

Reply | Quote

Thanks. I found it. Stupid mistake
My range named count is =OFFSET(Data!$A$2,0,1,COUNT(Data!C:C),1)
Changed to
My range named count is =OFFSET(Data!$A$2,0,1,COUNT(Data!$C:$C),1)

Note the absolute range reference in $C:$C in the new version. I thought I had done array math on dynamic ranges before!

Reply | Quote

You made your named range a bit too dynamic indeed.
If you defined the range as

=OFFSET(Data!$A$2,0,1,COUNT(Data!C:C),1)

whilst in cell A1 and then had the formula in cell B1, to cell B1 your named range is:

=OFFSET(Data!$A$2,0,1,COUNT(Data!D:D),1)

Reply | Quote