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

Use the ifs function to return 5 if cell a5 contains the value 3, and return a 6 if it contains any other number.
Excel if range of cell contain a text put a cell value in ✓ - Forum - Excel
SUM IFS function ✓ - Forum - Excel
Excel function question - Forum - Excel
Using the if function that returns a value of yes ✓ - Forum - Office Software
Excel "IF" function w/ date in test cell ✓ - Forum - Excel

1 reply

Answer 1 / 1

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."

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

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

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

Subscribe To Our Newsletter!