SUM Range based on date

E

edwardpestian

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
 
B

Bondi

edwardpestian said:
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.
Click to expand...
Hi Edward,

Maybe one way for you would be to use Sumproduct()

=SUMPRODUCT(--(A5:AD5<=A1),A6:AD6)

Regards,
Bondi
 
E

edwardpestian

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
 
B

Bondi

edwardpestian said:
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


--
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing Sum Range Based on Date Chosen from Drop Down List 1
Sum number range ignoring numbers formatted as text 0
Find last date of sold stock and sold price. 2
sum of text positions 10
Sum a range based on another range of cells 8
Help with SUMPRODUCT & converting date to value 1
if then based on date range 2
Counting number of rows between date range and meeting 2 string criteria 11

Top