Adding values standing in columns

Microsoft Excel 2000
March 8, 2009 at 16:29:36
Specs: Windows XP
Hi. I have a system where in column a there are dates for 1 month. In column D there is for every day the total worked time and in E the total earned wage overthis date. Somewhere next to it i want to be able to type any two dates and so that all the values between and including these dates are included. Both dates are from the existing dates in the table.

See More: Adding values standing in columns

Report •

March 8, 2009 at 21:06:04
I think you could use a Dynamic Range Name to do this. I tried this and it seems to work.


Dates are in A1:A31
Hours are in B1:B31
The first date you want to match is in C1
The last date you want to match is in D1

Pull down Insert...Name...Define

In the Names In Workbook box enter MyHours
In the Refers to box enter this formula:

=OFFSET($B$1, MATCH($C$1,$A1:$A$31)-1, 0,(MATCH($D$1,$A1:$A$31)-MATCH($C$1,$A1:$A$31)+1))

To use this Dynamic Range Name, enter =SUM(MyHours) in a cell.

By using the formula given, the range MyHours will be determined by the location of the dates in A1:A31 that match the dates in C1 and D1. C1 will determine the row value and D1 will determine the height value.


Report •
Related Solutions

Ask Question