Sum ifs function in excel

Solved/Closed
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013
- Nov 27, 2013 at 06:22 AM
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013
- Nov 28, 2013 at 06:55 AM
Is it possible to have a criteria range for a range of dates and have it add for a date less that the specified criteria?


11/1/2013 - 500
11/2/2013 - 400
11/5/2013 - 250
11/6/2013 - 325

from the date above..for example i want to add all those numbers that falls between 11/1/2013 and 11/4/2013...and so on...

Please help me.

Thanks

Ronald

1 reply

aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Nov 27, 2013 at 07:09 AM
Hi,

You should use the SUMPRODUCT() formula.

Suppose that your data are written like that on your sheet :


To add all numbers that fall between 11/01/2013 and 11/04/2013, use this formula :
=SUMPRODUCT(($A$2:$A$5>=DATEVALUE("11/01/2013"))*($A$2:$A$5<=DATEVALUE("11/04/2013"))*$B$2:$B$5)

Regards

"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
1
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013

Nov 28, 2013 at 02:34 AM
Thanks, it works perfectly !!!

Another question, from the solution above, would it possible to have a cell 2 cells indicating the dates of inclusive? so that I don't need to change the formula. Just change the date range? and it will automatically updates the result..

Thanks
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Nov 28, 2013 at 04:43 AM
Hi,

For example, if cell D2 contains the first date and E2 the second, you should use this formula :
=SUMPRODUCT(($A$2:$A$5>=DATEVALUE(TEXT(D2,"dd/mm/yyyy")))*($A$2:$A$5<=DATEVALUE(TEXT(E2,"dd/mm/yyyy")))*$B$2:$B$5)

Regards
0
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013

Nov 28, 2013 at 06:55 AM
Aquarelle,

Thank you very much !!! You're really great !!!..

Ronald
0