Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
I have a range of dates for the month of June. A5:AD5. I have corresponding data below each date. A6:AD6. I need to sum the data values based on the date in a different cell. For example, if the date in A1=06/22/2006, then I need to sum the values in the Range A6:AD6 for the dates up to and including the 22nd. Not sure where to start with this one. Thanks. ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=554637 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
=SUMIF(A5:AD5,A1,A6:AD6)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "edwardpestian" wrote in message news:edwardpestian.29tafd_1150993233.6508@excelfor um-nospam.com... I have a range of dates for the month of June. A5:AD5. I have corresponding data below each date. A6:AD6. I need to sum the data values based on the date in a different cell. For example, if the date in A1=06/22/2006, then I need to sum the values in the Range A6:AD6 for the dates up to and including the 22nd. Not sure where to start with this one. Thanks. ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=554637 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
edwardpestian wrote: I have a range of dates for the month of June. A5:AD5. I have corresponding data below each date. A6:AD6. I need to sum the data values based on the date in a different cell. For example, if the date in A1=06/22/2006, then I need to sum the values in the Range A6:AD6 for the dates up to and including the 22nd. Not sure where to start with this one. Hi Edward, Maybe one way for you would be to use Sumproduct() =SUMPRODUCT(--(A5:AD5<=A1),A6:AD6) Regards, Bondi |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
Hi
I've used diferent ranges but the principle is the same. My date is in A4 =SUMIF(A1:S1,"<="&A4,A2:S2) "edwardpestian" wrote in message news:edwardpestian.29tafd_1150993233.6508@excelfor um-nospam.com... I have a range of dates for the month of June. A5:AD5. I have corresponding data below each date. A6:AD6. I need to sum the data values based on the date in a different cell. For example, if the date in A1=06/22/2006, then I need to sum the values in the Range A6:AD6 for the dates up to and including the 22nd. Not sure where to start with this one. Thanks. ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=554637 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
SUMIF(A5:AD5,A1,A6:AD6) only returns the data for the date in A1. Similiar to an HLOOKUP(A1,A5:AD5,A6:AD6,2) However, the SUMPRODUCT function does work as expected. Would someone be so kind as to explain to me the SUMPRODUCT function below in plain English. I understand what is happening inside the parenthesis (A5:AD5<=A1),A6:AD6). However, I'm not sure about the leading --. =SUMPRODUCT(--(A5:AD5<=A1),A6:AD6) Thanks for the help. ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=554637 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
edwardpestian wrote: SUMIF(A5:AD5,A1,A6:AD6) only returns the data for the date in A1. Similiar to an HLOOKUP(A1,A5:AD5,A6:AD6,2) However, the SUMPRODUCT function does work as expected. Would someone be so kind as to explain to me the SUMPRODUCT function below in plain English. I understand what is happening inside the parenthesis (A5:AD5<=A1),A6:AD6). However, I'm not sure about the leading --. =SUMPRODUCT(--(A5:AD5<=A1),A6:AD6) Thanks for the help. ep -- Hi Edward The SUMPRODUCT Summs the product of the two arrays. The return from the (A5:AD5<=A1) would be something like TRUE, TRUE, FALSE Summing of that would be difficult.. So the --(A5:AD5<=A1) returns 1,1,0 and the summing can take place.. Regards, Bondi |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "edwardpestian" wrote in message news:edwardpestian.29tbtc_1150995006.4094@excelfor um-nospam.com... SUMIF(A5:AD5,A1,A6:AD6) only returns the data for the date in A1. Similiar to an HLOOKUP(A1,A5:AD5,A6:AD6,2) However, the SUMPRODUCT function does work as expected. Would someone be so kind as to explain to me the SUMPRODUCT function below in plain English. I understand what is happening inside the parenthesis (A5:AD5<=A1),A6:AD6). However, I'm not sure about the leading --. =SUMPRODUCT(--(A5:AD5<=A1),A6:AD6) Thanks for the help. ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=554637 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Range based on date
You should use
=SUMIF(A5:AD5,"<="&A1,A6:AD6) more efficient -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "edwardpestian" wrote in message news:edwardpestian.29tbtc_1150995006.4094@excelfor um-nospam.com... SUMIF(A5:AD5,A1,A6:AD6) only returns the data for the date in A1. Similiar to an HLOOKUP(A1,A5:AD5,A6:AD6,2) However, the SUMPRODUCT function does work as expected. Would someone be so kind as to explain to me the SUMPRODUCT function below in plain English. I understand what is happening inside the parenthesis (A5:AD5<=A1),A6:AD6). However, I'm not sure about the leading --. =SUMPRODUCT(--(A5:AD5<=A1),A6:AD6) Thanks for the help. ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=554637 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Power users need your help €“ calculation based on a date | Excel Worksheet Functions | |||
Bucketing data based on DATE Range criteria | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |