# Thread: Dynamic Range and Array Formula (Excel 2003 SP2)

1. ## Dynamic Range and Array Formula (Excel 2003 SP2)

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.

2. ## Re: Dynamic Range and Array Formula (Excel 2003 SP2)

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.

3. ## Re: Dynamic Range and Array Formula (Excel 2003 SP2)

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!

4. ## Re: Dynamic Range and Array Formula (Excel 2003 SP2)

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),1)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•