Sumproduct + Name Range

Hi all,

Your help with this would be greatly appreciated !!!!

I have created the sumproduct bellow and it works perfect :-)

=SUMPRODUCT((Data!$J$2:$J$6721="Senior")*(Data!$D$2:$D$6721="Non Project")*(Data!$E$2:$E$6721="Skills Improvement")*1)

However I need the columns to expand down if a new entry to the table is added. So i created a named range by clicking on

Insert | Name | define

This range has the name of

Job_Des

In the refares to box of the range i entered a OFFSET statement so the Name range grows as new entries are added as shown bellow

=OFFSET(Data!$J$2,0,0,MATCH("*",Data!$J:$J,-1),1)

Now i need to change the Summproduct statement so that it uses the name range insted of

Data!$J$2:$J$6721

I guesed the Sumproduct would look something like this

=SUMPRODUCT((Excelbreakdown.xls!Job_Des="Senior")*(Data!$D$2:$D$6721="Non Project")*(Data!$E$2:$E$6721="Skills Improvement")*1)

However the

Excelbreakdown.xls!Job_Des

does not seem to work it gives me an N/A error

Hope this makes sence - Any help would be FAB

Thanks
Martin

This is what I would do...
First I would test the length of Offset range [=counta(..)] since #N/A will occur if the range lengths are different.
Then I would try the usual Error seeking methods:
- reduce the size of my range - as a test - to see where the error occurs
- and then apply F9 to the reduced formula range - to see the true values - if an error still exists (http://techrepublic.com.com/5100-6270-1033815.html#)

Hope it can help - because apart from that - it looks like it should work.

Ola Sandström