sum if column heading in in past
 

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

sum if column heading in in past
 

=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)



"Kirstie Adam" (nospam) wrote in message
...
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

sum if column heading in in past
 

perfect thanks!

"Bob Phillips" wrote in message
...
=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)



"Kirstie Adam" (nospam) wrote in message
...
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

sum if column heading in in past
 

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


"Kirstie Adam" (nospam) wrote in message
...
perfect thanks!

"Bob Phillips" wrote in message
...
=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)



"Kirstie Adam" (nospam) wrote in message
...
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

sum if column heading in in past
 

=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)



"Kirstie Adam" (nospam) wrote in message
...
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


"Kirstie Adam" (nospam) wrote in message
...
perfect thanks!

"Bob Phillips" wrote in message
...
=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)



"Kirstie Adam" (nospam) wrote in message
...
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

sum if column heading in in past
 

so simple! thanks!

"Bob Phillips" wrote in message
...
=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)



"Kirstie Adam" (nospam) wrote in message
...
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


"Kirstie Adam" (nospam) wrote in message
...
perfect thanks!

"Bob Phillips" wrote in message
...
=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)



"Kirstie Adam" (nospam) wrote in message
...
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