Adding values in first "X" number of rows dynamically

[MattyP]

Hi -
I have a data worksheet which has item sub-totals in column F (each row is a
different item). In a different worksheet, I have a a few other calculations
going on, to determine # of resources available (this will vary, based on
data entered elsewhere). What I'd like to be able to do is only add the
sub-totals from "X" number of rows, depending on the # of resources available
(which can change).

So, for example, if I've determined to have 10 resources available, I'd like
to only calculate/sum the sub-totals from the first 10 rows. However, if
this changed to 12 resources, I'd like to be able to dynamically come up with
a total from the first 12 rows instead. Does that make sense (and is
something like that possible, in Excel ?) Any ideas would be most
appreciated !

Thanks for your help,
- Matt

 

[Luke M]

You can use the OFFSET function. The 4th argument controls height (aka,
amount of rows).

The following sums A1:A10
=SUM(OFFSET($A$1,0,0,10,1)
To sum A1:A12
=SUM(OFFSET($A$1,0,0,12,1)

Note that you could change the one number to a cell reference, to make the
formula easier to update.

 

[MattyP]

Worked like a charm........THANK YOU !!

- Matt

You can use the OFFSET function. The 4th argument controls height (aka,
amount of rows).

The following sums A1:A10
=SUM(OFFSET($A$1,0,0,10,1)
To sum A1:A12
=SUM(OFFSET($A$1,0,0,12,1)

Note that you could change the one number to a cell reference, to make the
formula easier to update.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[T. Valko]

Another one...

C1 = your number variable for how many rows to include in the calculation
A:A = range to calculate

=SUM(A1:INDEX(A:A,C1))

If C1 is empty the formula will calculate the entire range.