# Copy and Pasting Value into Formula

#### dsebrin

##### New Member
What I'm trying to do is using the sumproduct function have the formula reference a specific # in the cell range.

Here is formula
=SUMPRODUCT((\$B\$2:\$B\$2="Y")*(\$W\$2:\$W\$2=W2)*(\$M\$2:\$M\$2))

What I want is for for the range to go from the beginning record to the max number of records in the spreadsheet

End result is this, but am wondering if there is a way to reference the max # of rows in the spreadsheet without manually doing so
=SUMPRODUCT((\$B\$2:\$B\$402="Y")*(\$W\$2:\$W\$402=W2)*(\$M\$2:\$M\$402))

VBA CODE?????
Thanks! Dan

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### ljungren13

##### Board Regular
You could add dynamic named ranges into the sumproduct

=SUMPRODUCT(--(Name1="Y"),--(Name2="W2"),(Name3))

Where Name1, Name2 and Name3 are named ranges with these formulas as there sources

Name1:
Source: =OFFSET(Sheet1!\$B\$2,0,0,COUNTA(Sheet1!\$B:\$B)-1,1)

Name2:
Source: =OFFSET(Sheet1!\$W\$2,0,0,COUNTA(Sheet1!\$W:\$W)-1,1)

Name2:
Source: =OFFSET(Sheet1!\$M\$2,0,0,COUNTA(Sheet1!\$M:\$M)-1,1)

#### dsebrin

##### New Member
How do I create the name1, name2? Do I actually put the offset formulas in a specific cell? Also how does this work in terms of space?? Do you think using the offset will make the spreadsheet run faster or slower?

Thanks for you help!!!!!!!!

#### ljungren13

##### Board Regular
Creating named ranges for excel 2003:

Insert > Name > Define

Type the Name in the Names area in this case "Name 1"

Then copy the offset formula I provided and paste that where it says "Refers To" and click add

do this for all 3 named ranges and test your formula

if you do have excel 2007 you should be able to reference a whole column using sumproduct:
=SUMPRODUCT((\$B:\$B="Y")*(\$W:\$W=W2)*(\$M:\$M))

#### dsebrin

##### New Member
I have found out how to enter the name1,2,3. Thanks! Does anyone else have any other suggestions to how can make the spreadsheet work as speedy as possible with another type of formula? I'm going to be working with thousands of lines.

Tks!