Hi Ilgar!
Thanks for your solution. I think isn’t a good idea to use a mm-dd-yyyy format in dates, because it depends on regional configuration of every computer. The recommendation is to use the DATE function, or just use the “Generic Format” (yyyy-mm-dd) that works in every regional configuration.
Your formulas could be written like this:
=SUMIFS(M5:M49,L5:L49,"*apple*",L5:L49,"<>pineapple",P5:P49,"<2021-09-09")
=SUMIFS(M5:M49,L5:L49,"*bananas*",P5:P49,">=2021-08-31",P5:P49,"<=2022-02-18")
Blessings!
1 Like
Felt great working with Dates and Conditional Aggregation Functions!
Conditional Summation
Apples:
=SUMIFS($B$5:$B$49,$A$5:$A$49,"*apple*",$E$5:$E$49,"<"&DATE(2021,9,9))
Bananas
=SUMIFS($B$5:$B$49,$A$5:$A$49,"*bananas*",$E$5:$E$49,">="&DATE(2021,8,31),$E$5:$E$49,"<="&DATE(2022,2,18))
Dynamic Calculations
I’ve created a table where the user can specify the fruit and the period start and end dates to calculate the total kgs sold.
=SUMIFS($B$5:$B$49,$A$5:$A$49,"*"&J11&"*",$E$5:$E$49,">="&DATE(YEAR(J9),MONTH(J9),DAY(J9)),$E$5:$E$49,"<="&DATE(YEAR(J10),MONTH(J10),DAY(J10)))
Sharing the workbook here:
Challenge #1_Homesh Agarwal.xlsx (16.7 KB)
1 Like
@IlgarZarbaliyev - If we consider the statement “All apples until September 9, 2021” then the date “09-09-2021” should be included in the timeframe. Isn’t it?
=SUMIFS($M$5:$M$49,$L$5:$L$49,“apple”,$P$5:$P$49,“<=09-09-2021”)
By the way here’s is my submission - Challenge #1.xlsx (8.3 MB)
1 Like
Thank you for your solution. Until means the time is not included.
Answer:
=SUMIFS($M$5:$M$49,$L$5:$L$49,“Apple”,$P$5:$P$49,“<2021-09-09”)
=SUMIFS($M$5:$M$49,$L$5:$L$49,“Bananas”,$P$5:$P$49,“>=2021-08-31”,$P$5:$P$49,“<=2022-02-18”)
Challenge #1.xlsx (8.3 MB)