Menu
  1. Accueil
  2. Forum
  3. Office Software
  4. Excel

Sum ifs function in excel [Solved/Closed]

Report
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013
 -
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013
 -
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
See more

1 reply

Answer 1 / 1
Posts
7241
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
November 22, 2019
 490
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."
Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4149 users have said thank you to us this month

Report
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013
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
Report
aquarelle
Posts
7241
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
November 22, 2019
 490
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
Report
rtcorpuz
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
November 28, 2013
Aquarelle,

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

Ronald
Ask a question