Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding amounts based on a conditional date | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
52 week average based on date | Excel Discussion (Misc queries) | |||
LINK ONE ROW BASED ON CONTENTS OF A COLUMN WITHIN THE ROW (DATE) | Excel Worksheet Functions | |||
Show a date based on today | Excel Worksheet Functions |