MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SUM over a date range


Posted by mAT on September 26, 2001 8:23 AM

I have two columns(A and B). The first (A) gives daily dates (1st Jan to 31st Dec), the second (B) gives a variable number.

I would like to input two more colums (C and D) with date1 and date2 which I can vary at will. How do I sum over two specified dates?

Best regards
Mat


Posted by mAT on September 26, 2001 8:25 AM

I have two columns(A and B). The first (A) gives daily dates (1st Jan to 31st Dec), the second (B) gives a variable number.

I would like to input two more colums (C and D) with date1 and date2 which I can vary at will. How do I sum column B over the two specified dates?

Best regards
Mat


Posted by mAT on September 26, 2001 8:25 AM

I have two columns(A and B). The first (A) gives daily dates (1st Jan to 31st Dec), the second (B) gives a variable number.

I would like to input two more colums (C and D) with date1 and date2 which I can vary at will. How do I sum column B over the two specified dates?

Best regards
Mat


Posted by IML on September 26, 2001 8:33 AM

Assuming you have your start date in C1 and end date in D1, and your range extends from A1:B100, you could use
=SUM((A1:A100>=C1)*(A1:A100<=D1)*(B1:B100))

for this array formula to work, hit enter while control and shift are depressed.
If you are copying and pasting, hit F2 after pasting and then control shift enter.

good luck


Posted by IML on September 26, 2001 8:33 AM

Assuming you have your start date in C1 and end date in D1, and your range extends from A1:B100, you could use
=SUM((A1:A100>=C1)*(A1:A100<=D1)*(B1:B100))

for this array formula to work, hit enter while control and shift are depressed.
If you are copying and pasting, hit F2 after pasting and then control shift enter.

good luck


Posted by Aladin Akyurek on September 26, 2001 8:34 AM

I understand that you have dates in A and numbers in B.

And you want to enter a date in C1 and another in D1 (where D1 > C1)

You want probably sum the values in B that corresponds to date in A which must be a date that falls between dates in C1 and in D1.

In E1 enter: =SUMPRODUCT(($A$1:$A$100 > C1)*($A$1:$A$100 < D1)*($B$1:$B$100))

If you have more criterion dates in C and in D, copy down the above formula as as far needed.

Is this what you are looking for?

Aladin

==========


Posted by Aladin Akyurek on September 26, 2001 8:34 AM

I understand that you have dates in A and numbers in B.

And you want to enter a date in C1 and another in D1 (where D1 > C1)

You want probably sum the values in B that corresponds to date in A which must be a date that falls between dates in C1 and in D1.

In E1 enter: =SUMPRODUCT(($A$1:$A$100 > C1)*($A$1:$A$100 < D1)*($B$1:$B$100))

If you have more criterion dates in C and in D, copy down the above formula as as far needed.

Is this what you are looking for?

Aladin

==========


Posted by mAT on September 27, 2001 12:47 AM

Many Thanks both to Aladin and IML. It works great!

Regards
Mat

Posted by mAT on September 27, 2001 12:47 AM

Many Thanks both to Aladin and IML. It works great!

Regards
Mat