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.
Gta 5 download apk pc - Download - Action and adventure
Excel function question - Excel Forum
If cell contains text then return value multiple conditions ✓ - Excel Forum
If cell contains date then return value ✓ - Office Software Forum
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum

1 response

Answer 1 / 1

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 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 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 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

Similar discussions

Excel if range of cell contain a text put a cell value in

Excel function question

Convert number into words in excel 2007

If a cell contains any text then add a value (with dates)

If a cell contains any text then add a value

mysql - Counting number of occurrences of value in multiple columns

Similar discussions

Subscribe To Our Newsletter!