Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of a range that varies
Good day,
I am making the sum of a range that varies in length. For example Cell(1, B) = SUM(A1:A10) The cells A1 to A10 are a database that is updated daily, and the number of rows varies also daily. So one day, the sum could be A1:A7, and the next day it is A1:A12. Also, the range sometimes needs to start at A1, sometimes A3 or A5, it is never the same. I would need a way to have the last value of my range as a variable instead of a fix value. The final formula should ideally fit in only one cell. I am thinking of using the count() or the Row() function within my SUM formula but nothing works. -- Thanks Sebastien |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of a range that varies
Hi Sebastien
Since you have a changing start and changing end to your range, you would need a method of telling the formula where both of these values are. With your formula in B1, set the start row in C1 and the end row in D1 =SUM(INDEX(A:A,C1):INDEX(A:A,D1)) With 5 in C1 and 17 in D1 it would be the same as =SUM(A5:A17) -- Regards Roger Govier "Sebastien" wrote in message ... Good day, I am making the sum of a range that varies in length. For example Cell(1, B) = SUM(A1:A10) The cells A1 to A10 are a database that is updated daily, and the number of rows varies also daily. So one day, the sum could be A1:A7, and the next day it is A1:A12. Also, the range sometimes needs to start at A1, sometimes A3 or A5, it is never the same. I would need a way to have the last value of my range as a variable instead of a fix value. The final formula should ideally fit in only one cell. I am thinking of using the count() or the Row() function within my SUM formula but nothing works. -- Thanks Sebastien |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight same part numbers with high price varies among themselv | Excel Discussion (Misc queries) | |||
Font displayed varies from selection | Excel Discussion (Misc queries) | |||
Find character when the length varies | Excel Discussion (Misc queries) | |||
PPMT Function with varies interest rate | Excel Worksheet Functions | |||
Border thickness varies when I paste Excel cells into Word. | Excel Discussion (Misc queries) |