Conditional summation in Excel is the process of summing up a range of cells based on a certain condition. It involves using specific functions which allow you to specify one or more criteria that must be met in order for the cells to be included in the sum.
Goals
Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.
Task
Make the necessary calculations in cells T5 and T6, using the most appropriate function in Excel, within the given conditions. Compare your answers with the values in cells V5 and V6.
Submission
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.
Hi Ilgar,
Thanks for the workout, it is great to practice my excel skills again. I note that pineapple is outside the date range otherwise that would be included and given it was not case sensitive I was not sure how to deal with that scenario.
=SUMIFS($M:$M,$L:$L,βAppleβ,$P:$P,β<9/9/2021β)
=SUMIFS($M:$M,$L:$L,βBananasβ,$P:$P,β>=8/31/2021β,$P:$P,β<=2/18/2022β)
Note there are β*β in the folumaes arround apple and bananas which doesnβt show up on the solution
My formula
=SUMIFS(M5:M49,L5:L49,"*apple*",P5:P49,"<9/9/21")
=SUMIFS(M5:M49,L5:L49,"*banana*",P5:P49,">=31/8/21",P5:P49,"<=18/2/22")
Alternative Solution
=SUM(FILTER(M5:M49,P5:P49<DATE(2021,9,9)*ISNUMBER(SEARCH("apple",L5:L49))))
=SUM(FILTER(M5:M49,(P5:P49>=DATE(2021,8,31))*(P5:P49<=DATE(2022,2,18))*(ISNUMBER(SEARCH("banana",L5:L49)))))
Thank you for participating in the Excel Challenge related to conditional summation! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to perform conditional summation in Excel.