sum if column heading in in past

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

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
 
=SUMPRODUCT((A1:H1<=TODAY())*(A2:H10))

adjust the ranges to suit.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
perfect thanks!

Bob Phillips said:
=SUMPRODUCT((A1:H1<=TODAY())*(A2:H10))

adjust the ranges to suit.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
 
Bob,

This really helped, but now i have come across another problem!

I want to use this code in over 300 rows of data, but the first half of the
formula is to stay the same

eg.

=sumproduct((1st range <=Today())

the range will always be the same as it is a column heading row

and the second range which it is to calculate will increase by one as i go
down each row.

At the moment when i copy and paste it increase each range reference by one,
and i am manually having to change the first range reference back to what it
should be for each one! It's taking ages!

Is there a way of copying this formula into all these rows and have it keep
the first range the same and the second range change?

Kirstie
 
=SUMPRODUCT(($A$1:$H$1<=TODAY())*(A2:H10))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
so simple! thanks!

Bob Phillips said:
=SUMPRODUCT(($A$1:$H$1<=TODAY())*(A2:H10))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
 
Back
Top