Formula rref

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
Ive got a formula with a range
eg =sum(a2:a5)

what im wanting to do is have the end row value in a cell eg C5 with a value of 6.

and reference that in the formula eg
=sum(a2:a&C5)
but that doesn't seem to work any Ideas?

Brett
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-11-06 18:08, brettvba wrote:
Ive got a formula with a range
eg =sum(a2:a5)

what im wanting to do is have the end row value in a cell eg C5 with a value of 6.

and reference that in the formula eg
=sum(a2:a&C5)
but that doesn't seem to work any Ideas?

Brett

=SUM(INDIRECT("A2:A"&C5))
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi brett:

=SUM(INDIRECT("a2:a"&C5))

or

=SUM(OFFSET(A2,0,0,C5-1))

would both do it.

paddy
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

Or...

=SUM(A2:INDEX(A:A,C5))
 

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
ok thanks Mark I will give you the formula im trying to get this to work on

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((Christchurch!$B$2:$B$18880=$A3)+0,(Christchurch!$F$2:$F$18880/Christchurch!$G$2:$G$18880))

where im trying to replace the 18880 with a cell value?

Brett
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-11-06 18:22, brettvba wrote:
[...]the formula im trying to get this to work on

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((Christchurch!$B$2:$B$18880=$A3)+0,(Christchurch!$F$2:$F$18880/Christchurch!$G$2:$G$18880))

where im trying to replace the 18880 with a cell value?[...]

Unless you want to make selections at will from your data range, try to define a single dynamic named range instead, say DTable, spanning column B to G and use

=SUMIF(INDEX(DTable,0,1),$A3,INDEX(DTable,0,4)+SUMPRODUCT((INDEX(DTable,0,1)=$A3)+0,(INDEX(DTable,0,5)/INDEX(DTable,0,6)))

A reference among many on how to create DTable is...

http://www.mrexcel.com/board/viewtopic.php?topic=16207&forum=2&7
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-11-06 18:22, brettvba wrote:
ok thanks Mark I will give you the formula im trying to get this to work on

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((Christchurch!$B$2:$B$18880=$A3)+0,(Christchurch!$F$2:$F$18880/Christchurch!$G$2:$G$18880))

where im trying to replace the 18880 with a cell value?

Brett

=SUMIF(Christchurch!$B:$B,$A3,Christchurch!$E:$E)+SUMPRODUCT((INDIRECT("Christchurch!B2:B"&C5)=$A3)+0,INDIRECT("Christchurch!F2:F"&C5)/INDIRECT("Christchurch!G2:G"&C5))
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

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