Adding values in first "X" number of rows dynamically
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 |
Adding values in first "X" number of rows dynamically
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!* "MattyP" wrote: 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 |
Adding values in first "X" number of rows dynamically
Worked like a charm........THANK YOU !!
- Matt "Luke M" wrote: 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!* "MattyP" wrote: 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 |
Adding values in first "X" number of rows dynamically
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. -- Biff Microsoft Excel MVP "MattyP" wrote in message ... 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 |
All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com