Computed Cell Reference in an Array Formula

  • Thread starter Thread starter Joe Blow
  • Start date Start date
J

Joe Blow

Hi,

I would like to use the last non-blank row as a reference in an array
formula.

For instance:

My last non-blank row is 46, I have a cell, x2 that computes the 46.
Now I would like to use it as an array reference.

In example:

{=sum(if($G$10:$G$200<=13,$D$10:$D200,0))}

In this case I would like to replace the 200 reference with the
computed value from x2.

Any ideas?

Thanks for helping,
Joe
 
You could use INDIRECT:

=SUM(IF(INDIRECT("$G$10:$G$"&X2)<=13,INDIRECT
("$D$10:$D"&X2,0))

Array-entered.

HTH
Jason
Atlanta, GA
 
Perfect, Thanks Jason!

Joe

You could use INDIRECT:

=SUM(IF(INDIRECT("$G$10:$G$"&X2)<=13,INDIRECT
("$D$10:$D"&X2,0))

Array-entered.

HTH
Jason
Atlanta, GA
Click to expand...
 
=SUMIF($G$10:INDEX($G:$G,X2),"<=13",$D$10:INDEX($D:$D,X2))
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed 0
Mapping integers to strings 5
Cell reference within array 10
Scrabble Value calculation for Welsh words 0
row number of last non-blank cell 14
Excel Wildcard in Excel array formula 0
Create an array of separate cells 1
How can I use the * wildcard in an array formula 4

Back
Top