Excel Workout 1 - Conditional Summation

Workouts Excel
1

Welcome to Excel Workout #1!

Difficulty Level:

1 of 5 Star Rating
1 of 5 Star Rating720×150 5.52 KB

This week’s challenge is designed to test your knowledge on conditional summation.

image
image2917×858 250 KB

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.

image
image735×266 25.6 KB

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
2

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

image
image873×67 3.95 KB

Challenge #1.xlsx (8.3 MB)

2 Likes
3

Hi @KimC ,

Thank you for your reply.

1 Like
4

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
6

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
7

Hi @Keith,

Thank you for your reply.

Will you upload your solution file?

Thaks in advance.

8

done

thanks Keith

1 Like
9

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
10

Hi @QuadriAtharuOlayinka,

Thank you for your solution.

1 Like
11

You are most welcome

12

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
[Challenge #1.cleaned_Oksana Romanova.xlsx|attachment](upload://g0pYcMGaXoqX8BQwmp8uRGkBSi9.xlsx) (8.3 MB)
-Aug-21",P5:P49,"<=18-Feb-22")

Challenge #1.cleaned_Oksana Romanova.xlsx (8.3 MB)

2 Likes
13

Hi Oksana,

Thank you for your solution.

14

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
15

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
18

=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
19

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

image
image741×77 2.76 KB

1 Like
21

If I am asked about apples, how can I include pineapple?

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
25

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

Paul

2 Likes
26

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
27

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:

Workout 1 Solution
Workout 1 Solution1646×653 42 KB

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