Copy and Pasting Value into Formula

dsebrin

New Member
Joined
Jul 26, 2010
Messages
8
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
 

Some videos you may like

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
Joined
Jun 4, 2010
Messages
195
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

New Member
Joined
Jul 26, 2010
Messages
8
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
Joined
Jun 4, 2010
Messages
195
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
Joined
Jul 26, 2010
Messages
8
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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,943
Messages
5,508,270
Members
408,673
Latest member
CELER_

This Week's Hot Topics

Top