Copy and Pasting Value into Formula

[dsebrin]

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

 

[ljungren13]

You could add dynamic named ranges into the sumproduct

In your case:

=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]

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]

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]

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!