Hey all! Need some help on finding a total. I have a column for each month and an amount assigned to each month. I need a worksheet formula to find a total from January to the current month. For example, if the current month is June, sum the numbers from January through June. This should be easy but it's kicking my @@@. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Hi Larry,

Assuming your data is in columns A-L, in row 2, then try this:

=SUM(OFFSET(A2,0,0,1,MONTH(TODAY())))


Stu.

In a new Column enter the following formula:

=If(month(a1)<=10,d1,0)

a1 - this is where the month column is in your spreadsheet. Change this to the appropriate column.

d1 - this is where the Assigned Amount is in your spreadsheet. Change this to the appropriate column.

10- this represents the current month. So this is looking in cell A1 for Months from October and earlier. October is 10...obviously


After entering this formula in the first cell of the new column, Copy and Paste it to all the others. Then at the bottom of this column, Sum the column.

Thanks to both of you. In this particular situation...I went with Stu's formula. I will keep them both tucked away for future use!

Additionally, Index() gives you a non-volatile option, e.g.,

=SUM(A2:INDEX(A2:L2,1,MONTH(TODAY())))

Although Today() is volatile... Depends what you're really feeding this thing... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)