# Excel Workout 1 - Conditional Summation

Welcome to Excel Workout #1!

Difficulty Level:

This weekβs challenge is designed to test your knowledge on conditional summation.

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

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

Period

This workout will be released on Monday March 27, 2023, and the authorβs solution will be posted on Sunday April 02, 2023.

Ilgar Zarbaliyev

Challenge #1.xlsx (8.3 MB)

2 Likes

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.

My formulas are:

Summary

Challenge #1.xlsx (8.3 MB)

2 Likes

Hi @KimC ,

1 Like

Been a while since used Excel like this! Thanks for the challenge.

=SUMIFS(\$M\$5:\$M\$49,\$L\$5:\$L\$49,βAppleβ,\$P\$5:\$P\$49,β<9/9/2021β)
=SUMIFS(\$M\$5:\$M\$49,\$L\$5:\$L\$49,βBananaβ,\$P\$5:\$P\$49,β>=31/08/2021β,\$P\$5:\$P\$49,β<=18/02/2022β)

Challenge #1.xlsx (8.3 MB)

1 Like

Hi There
Thanks for the refresher

Here is my solution

=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

Thanks again
Keith
Excel Workout Challenge #1 keith smith.xlsx (8.3 MB)

1 Like

Hi @Keith,

done

thanks Keith

1 Like

What a great way to start the day. thanks for the challenge. Keep them coming.
Challenge #1 - Quadri Solution.xlsx (8.3 MB)

``````

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)))))
``````
4 Likes

1 Like

You are most welcome

Thanks for brushing up on my Excel skills!

Here is my solution:

Summary
``````=SUMIFS(M5:M49,L5:L49,"*Apple*",P5:P49,"<9-Sep-21")

=SUMIFS(M5:M49,L5:L49,"*bananas*",P5:P49,">=3
-Aug-21",P5:P49,"<=18-Feb-22")
``````
2 Likes

Hi Oksana,

AhmedMagdyChallenge #1.xlsx (8.3 MB)

[spoiler]=SUMIFS(M5:M49,L5:L49,ββ&L5&ββ,P5:P49,β<β&P22,L5:L49,β<>β&βPineappleβ)

=SUMIFS(M5:M49,L5:L49,ββ&βBananasβ&ββ,P5:P49,β<=β&P44,P5:P49,β>=β&P11)

Summary

[/spoiler]
Thank You so much, I really enjoyed the workout as it has been a long time not using excel.

2 Likes

Thanks for getting me to brush up on my excel skills but not your every day summation:

=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,βBananaβ,\$P\$5:\$P\$49,β>=2021-08-31β,\$P\$5:\$P\$49,β<=2022-02-18β)

Challenge #1.xlsx (8.3 MB)

1 Like

=SUMIFS(SoldKg,Description,βAppleβ,SaleDate,β>=β&DATE(2021,8,28),SaleDate,β<=β&DATE(2021,9,8))
=SUMIFS(SoldKg,Description,βbananasβ,SaleDate,β>=β&DATE(2021,8,31),SaleDate,β<=β&DATE(2022,2,18))
Challenge #1 Franziola Chiu.xlsx (8.3 MB)

1 Like

Hi, Iβm not using excel everyday as before, so this workouts will be great to refresh and even learn more.
Thanks.

Here is my solution.

Challenge #1_AA.xlsx (8.3 MB)

Formulas

1 Like

Still, I am going with your logic and have matched my answer.

I have used this method.

``````1. =SUMIFS(M5:M49, L5:L49, "*apple*", P5:P49, "<" & DATE(2021, 9, 9))

2. =SUMIFS(
M5:M49,
L5:L49, "*bananas*",
P5:P49, ">=" & DATE(2021, 8, 31),
P5:P49, "<=" & DATE(2022, 2, 18)
)

``````

Challenge #1.xlsx (8.3 MB)

1 Like

@QuadriAtharuOlayinka Great job. IFS is so much better than doing nested IF statements. Good choice!

Paul

2 Likes

Thanks for the challenge! Hereβs my solution:

=SUMIFS(M5:M49;L5:L49;βAppleβ;P5:P49;β<=08/09/2021β)
=SUMIFS(M5:M49;L5:L49;βbananasβ;P5:P49;β>=31/08/2021β;P5:P49;β<=18/02/2022β)

Challenge #1.xlsx (8.3 MB)

1 Like

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.

Here is my solution to Workout #1:

The nuances to watch out for in this challenge were the following:

• Using an asterisk (*) wildcard character that uses matches any number of characters

• Using β<>β Text Criteria Record that contain any nonblank entry

Here are the formulas:

1. All Sold apples in kg until September 9, 2021
• =SUMIFS(M5:M49,L5:L49,βappleβ,L5:L49,β<>pineappleβ,P5:P49,β<09-09-2021β)
1. All Sold bananas in the period from 31 August 2021 to 18 February 2022
• =SUMIFS(M5:M49,L5:L49,βbananasβ,P5:P49,β>=08/31/2021β,P5:P49, β<=02/18/2022β)

You can find the file containing the solution below:

Challenge #1 with Solution.xlsx (8.3 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

1 Like