sumif based on date
 

I need a formula that will sum a row up to a certain date. In the table
below the date is in a1 and the data is in a4:e4

I have been trying but what I have is not working
=SUM(OFFSET(Sheet1!$A$4,0,0,MATCH(A1,Sheet1!$A$3:$ E$3,0)))


A B C D E

01/26/05

1/24/2005 1/25/2005 1/26/2005 1/27/2005 1/28/2005
1 2 3 4 5


I will appreciate any help,

Thanks

Todd

sumif based on date
 

Hi,

Use

=SUMIF($A$3:$E$3,A1,$A$4:$E$4)

Regards

Govind.

Todd wrote:

I need a formula that will sum a row up to a certain date. In the table
below the date is in a1 and the data is in a4:e4

I have been trying but what I have is not working
=SUM(OFFSET(Sheet1!$A$4,0,0,MATCH(A1,Sheet1!$A$3:$ E$3,0)))


A B C D E

01/26/05

1/24/2005 1/25/2005 1/26/2005 1/27/2005 1/28/2005
1 2 3 4 5


I will appreciate any help,

Thanks

Todd

sumif based on date
 

Try this slight amendment to your posted formula:

=SUM(OFFSET(Sheet1!$A$4,,,,MATCH(A1,Sheet1!$A$3:$E $3,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Todd" wrote in message
...
I need a formula that will sum a row up to a certain date. In the table
below the date is in a1 and the data is in a4:e4

I have been trying but what I have is not working
=SUM(OFFSET(Sheet1!$A$4,0,0,MATCH(A1,Sheet1!$A$3:$ E$3,0)))


A B C D E

01/26/05

1/24/2005 1/25/2005 1/26/2005 1/27/2005 1/28/2005
1 2 3 4 5


I will appreciate any help,

Thanks

Todd

sumif based on date
 

"Govind" wrote:
=SUMIF($A$3:$E$3,A1,$A$4:$E$4)

Perhaps not, in this instance, Govind

I read it from the OP's line:
... will sum a row up to a certain date

that OP wants to sum from 24-Jan to 26-Jan*
*the date specified in A1 in the sheet where his formula lies
Think his formula is on another sheet, whilst the source data is in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

sumif based on date
 

Max wrote:

"Govind" wrote:

=SUMIF($A$3:$E$3,A1,$A$4:$E$4)


Perhaps not, in this instance, Govind

I read it from the OP's line:

... will sum a row up to a certain date


that OP wants to sum from 24-Jan to 26-Jan*
*the date specified in A1 in the sheet where his formula lies
Think his formula is on another sheet, whilst the source data is in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

sumif based on date
 

Oops. sorry about that Max. I will slighly revise my formula in that case,

=SUMIF(Sheet1!A3:E3,"<="&A1,Sheet1!A4:E4)

Regards

Govind.

Max wrote:

"Govind" wrote:

=SUMIF($A$3:$E$3,A1,$A$4:$E$4)


Perhaps not, in this instance, Govind

I read it from the OP's line:

... will sum a row up to a certain date


that OP wants to sum from 24-Jan to 26-Jan*
*the date specified in A1 in the sheet where his formula lies
Think his formula is on another sheet, whilst the source data is in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

sumif based on date
 

You were just missing an extra comma ","
within the OFFSET to secure the width param
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

sumif based on date
 

=SUMIF(Sheet1!A3:E3,"<="&A1,Sheet1!A4:E4)

Yes, perhaps a good alternative here, Govind <g
It's much shorter ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---