software

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/20051/25/20051/26/20051/27/20051/28/2005
12345I will appreciate any help,

Thanks

ToddHi,

Use

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

Regards

Govind.

Todd wrote:

gt; I need a formula that will sum a row up to a certain date. In the table
gt; below the date is in a1 and the data is in a4:e4
gt;
gt; I have been trying but what I have is not working
gt; =SUM(OFFSET(Sheet1!$A$4,0,0,MATCH(A1,Sheet1!$A$3:$ E$3,0)))
gt;
gt;
gt; A B C D E
gt;
gt; 01/26/05
gt;
gt; 1/24/20051/25/20051/26/20051/27/20051/28/2005
gt; 12345
gt;
gt;
gt; I will appreciate any help,
gt;
gt; Thanks
gt;
gt; Todd
gt;

Try this slight amendment to your posted formula:

=SUM(OFFSET(Sheet1!$A$4,,,,MATCH(A1,Sheet1!$A$3:$E $3,0)))
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Toddquot; gt; wrote in message
...
gt; I need a formula that will sum a row up to a certain date. In the table
gt; below the date is in a1 and the data is in a4:e4
gt;
gt; I have been trying but what I have is not working
gt; =SUM(OFFSET(Sheet1!$A$4,0,0,MATCH(A1,Sheet1!$A$3:$ E$3,0)))
gt;
gt;
gt; A B C D E
gt;
gt; 01/26/05
gt;
gt; 1/24/2005 1/25/2005 1/26/2005 1/27/2005 1/28/2005
gt; 1 2 3 4 5
gt;
gt;
gt; I will appreciate any help,
gt;
gt; Thanks
gt;
gt; Todd
gt;
quot;Govindquot; wrote:
gt; =SUMIF($A$3:$E$3,A1,$A$4:$E$4)

Perhaps not, in this instance, Govind

I read it from the OP's line:
gt; ... 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
savefile.com/projects/236895
xdemechanik
---
Max wrote:

gt; quot;Govindquot; wrote:
gt;
gt;gt;=SUMIF($A$3:$E$3,A1,$A$4:$E$4)
gt;
gt;
gt; Perhaps not, in this instance, Govind
gt;
gt; I read it from the OP's line:
gt;
gt;gt;... will sum a row up to a certain date
gt;
gt;
gt; that OP wants to sum from 24-Jan to 26-Jan*
gt; *the date specified in A1 in the sheet where his formula lies
gt; Think his formula is on another sheet, whilst the source data is in Sheet1
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;

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

=SUMIF(Sheet1!A3:E3,quot;lt;=quot;amp;A1,Sheet1!A4:E4)

Regards

Govind.

Max wrote:

gt; quot;Govindquot; wrote:
gt;
gt;gt;=SUMIF($A$3:$E$3,A1,$A$4:$E$4)
gt;
gt;
gt; Perhaps not, in this instance, Govind
gt;
gt; I read it from the OP's line:
gt;
gt;gt;... will sum a row up to a certain date
gt;
gt;
gt; that OP wants to sum from 24-Jan to 26-Jan*
gt; *the date specified in A1 in the sheet where his formula lies
gt; Think his formula is on another sheet, whilst the source data is in Sheet1
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;

You were just missing an extra comma quot;,quot;
within the OFFSET to secure the width param
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
gt; =SUMIF(Sheet1!A3:E3,quot;lt;=quot;amp;A1,Sheet1!A4:E4)

Yes, perhaps a good alternative here, Govind lt;ggt;
It's much shorter ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---