I have a spreadsheet which has each month as a column heading, it will
always show the last 3 months and the next 6 months (every month we hide a
month and add a new month on)

eg. May-07, June 07, July 07, August 07, September 07

In each column i put what has been invoiced, and what is going to be
invoiced, so May might contain £100 and August £200.

I want a formula to put in a cell which will calculate all the months from
the current date back, but not future months. but i don't want to have to
amend the calulation every month!

i might not be making this clear, i hope someone can help!

Kirstie

If your first row contains the months (real dates, not text) and if you have
a sum row for each column at the bottom (in my example it is row 11), then
this formula:
=SUMPRODUCT(--(1:1<""),--(1:1<=NOW()),11:11)
will calculate the sum of all the months which are before today's date.

Let me know in case I have misunderstood your request!

--
Message posted via http://www.officekb.com