Hi friend! Thanks for joining me this week. This week, we are looking forward to the movie night I am hosting at my place. I sent out a survey (because I may or may not be that guy 👀) with snack options that fit my budget (of course) and I want to compute how many of each snack I have to buy prior to the meeting.
Also, my YouTube channel is back so please subscribe to it if you have not already.
TL;DR
1. Unique function to extract the unique snacks ordered. Formula:
=UNIQUE(list_of_snacks)2. COUNTIFS to count how many of each unique snack I must buy. Formula:
=COUNTIFS(range_with_all_snacks, specific_snack)You can also watch the video tutorial on my YouTube channel here instead.
Done!
For my readers, let's do this!
Here is what the final responses to the survey looked like:
Here is the Excel file if you want to follow along:
The DATA
So obviously this data is little enough for one to simply count manually and make all the necessary decisions. However, this is just an example so please play along.
The UNIQUE function for creating a list of unique snacks
UNIQUE is a dynamic array function that removes duplicate values from a range of values or a list. Check out my YouTube video on dynamic arrays; they are game-changers for the spreadsheet experience. UNIQUE takes only the desired range or list as an argument and returns the unique values within that range. In this case, the desired range is the list of snacks in column B.
Counting the number of the snacks with COUNTIFS
Now that we have the unique values, we want to place the count of each snack in the column adjacent to the snacks column. We will use the COUNTIFS function.
COUNTIFS takes 2 main arguments: the criteria range and the criterion. In our example, the criteria range is the original range of snacks where we want to count from. The criterion is a specific snack. The illustration will clear up the confusion you might be battling right now:
Based on this illustration, the critera_range is the original list of snacks. For the criteria, we start with the first cell and count the number of pizzas we need to order. Therefore, to get the criteria, we click inside the cell containing pizza and we discover that we need to order 3 pizzas. Since that is accurate, we copy the formula downwards, and Excel (or Google Sheets), moves downwards as well to Chevda, Popcorn and finally Groundnuts.
Conclusion
You may not be the type of person to whip out Excel to plan a party but perhaps you should try it. You will be amazed at how much you can do! Gotta go prep for this movie night. Wanted to watch Interstellar but I also do not want people to pitch tents in my house for all eternity, so I'll go with something else.
Have a good week!
Comments