| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to dynamically add up varying # of cells base on a value
How do I get a formula to decide on the # of cells to add up base on the
value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to dynamically add up varying # of cells base on a value
With 5 in B1 the following will sum the range A1:A5
=SUM(INDIRECT("A1:A"&B1)) if you change B1 to 10 it will sum the range A1:A10 "Felicia" wrote: How do I get a formula to decide on the # of cells to add up base on the value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to dynamically add up varying # of cells base on a value
Try this:
=SUM(A1:INDEX(A1:L1,n)) Where n = a number or a reference to a cell that holds the number. A5 = 6 =SUM(A1:INDEX(A1:L1,A5)) That will sum A1:F1. If A5 is an empty cell the formula will calculate the entire range. -- Biff Microsoft Excel MVP "Felicia" wrote in message ... How do I get a formula to decide on the # of cells to add up base on the value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to dynamically add up varying # of cells base on a value
Great, exactly what I needed. Thanks very much, Biff !
Felicia "T. Valko" wrote: Try this: =SUM(A1:INDEX(A1:L1,n)) Where n = a number or a reference to a cell that holds the number. A5 = 6 =SUM(A1:INDEX(A1:L1,A5)) That will sum A1:F1. If A5 is an empty cell the formula will calculate the entire range. -- Biff Microsoft Excel MVP "Felicia" wrote in message ... How do I get a formula to decide on the # of cells to add up base on the value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to dynamically add up varying # of cells base on a value
This works great, exactly what I needed. Thanks very much, Biff !
Felicia "T. Valko" wrote: Try this: =SUM(A1:INDEX(A1:L1,n)) Where n = a number or a reference to a cell that holds the number. A5 = 6 =SUM(A1:INDEX(A1:L1,A5)) That will sum A1:F1. If A5 is an empty cell the formula will calculate the entire range. -- Biff Microsoft Excel MVP "Felicia" wrote in message ... How do I get a formula to decide on the # of cells to add up base on the value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to dynamically add up varying # of cells base on a value
Hi, Sheeloo:
Thanks for the suggestion. Tried it out and if I understand this formula correctly, it seems to sums up range of cells in the same column (ie A1:A5) only. So, what if I needed to sum up range of cells in the same row (ex A1:L1) ? T. Valko also responded with the suggestion of using INDEX. Is that the approach for suming up cross the row or is there still possible option by using the INDIRECT function ? Just wanted to confirm. And eitherway, great to learn about this INDIRECT function as well as I'm sure I'll find use for it later. Felicia "Sheeloo" wrote: With 5 in B1 the following will sum the range A1:A5 =SUM(INDIRECT("A1:A"&B1)) if you change B1 to 10 it will sum the range A1:A10 "Felicia" wrote: How do I get a formula to decide on the # of cells to add up base on the value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to dynamically add up varying # of cells base on a value
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Felicia" wrote in message ... This works great, exactly what I needed. Thanks very much, Biff ! Felicia "T. Valko" wrote: Try this: =SUM(A1:INDEX(A1:L1,n)) Where n = a number or a reference to a cell that holds the number. A5 = 6 =SUM(A1:INDEX(A1:L1,A5)) That will sum A1:F1. If A5 is an empty cell the formula will calculate the entire range. -- Biff Microsoft Excel MVP "Felicia" wrote in message ... How do I get a formula to decide on the # of cells to add up base on the value in another cell. For example, column A to L contains the $saving for each months of the year from Jan to Dec. And say that savings for the first x months of the year could be carried over from the previous year; and that x value is reflected in column M. And I want to know the total $saving that is carried over from the previous year. So, how do get the formula to add up the first x number of months and put the value in column O. Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving Dec Saving # Months carried over Savings Carried Over 100 200 150 160 300 350 400 200 200 300 300 400 4 610 100 100 200 250 150 700 600 700 600 500 550 500 5 800 300 400 300 250 200 800 300 500 600 500 550 500 0 0 Thanks very much. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Varying selected cells returning value always to the same cell | Excel Discussion (Misc queries) | |||
| Adding cells that have varying contents | Excel Worksheet Functions | |||
| locking cells dynamically | Excel Worksheet Functions | |||
| Copying cells from varying rows | Excel Discussion (Misc queries) | |||
| concatenate with a varying range of cells | Excel Worksheet Functions | |||
Linear Mode
