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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,636
Messages
5,660,004
Members
418,542
Latest member
Sandy3503

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top