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

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

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

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

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

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

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

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