SUMPRODUCT

[Guest]

I have a sumproduct formula set up and it is working. the problem is that the
data it is pulling increases through out the month. When i try to set it up
to look at A:A it does not work. It gives me a #NUM!. If I put A1:A163 it
will work. The problem is today it may go down to 163 in a week it will need
to go down to 1000. How can I set this up so i don't have to keep changing
the formula?

 

[Don Guillett]

use a defined name for the range(s)
rngA=offset($a$1,0,0,counta($a:$a),1)
rngB=offset($a$1,0,0,counta($a:$a),2)

 

[Guest]

A couple of possible solutions...

1. A1:A65535
2. Dyanmic Named Ranges (even if you choose not to use this it is worth the
read)
http://www.cpearson.com/excel/named.htm