I wanted to point out two formula tips that maybe you're not familiar with. In addition to the below, I have added the class recordings for Weeks 12 and 13. Reminder that the passcode for the...

1 answer below »
I wanted to point out two formula tips that maybe you're not familiar with. In addition to the below, I have added the class recordings for Weeks 12 and 13. Reminder that the passcode for the recordings is Microsoft1! 
1. Section 1 - Filters - The start and end date dropdowns should filter the data to that specific date range
a. It should be obvious that you're going to be using a SUMIFS or AVERAGEIFS function for the Engagement and Video Views bullets within Section 1. In order for your data to filter based on a date range, you need reference the date dropdowns that you're creating. So, for example, if your sheet looks like the below, you might write your function like this: SUMIFS(C:C,B:B,">=F2",B:B,"<=F3"). This will return 0.
However, that won't work here since the date is a cell reference and not a value. If you wrote that as: SUMIFS(C:C,B:B,">=4/5/2021",B:B,"<=4/7/2021"), it would work flawlessly!
To get your formula to read the cell data, you need to write it a bit differently. The >= and <= need to be enclosed in the double quotes and that needs to be connected to the cell reference using an amperand (&). When you do that, you no longer need the quote after the cell reference. So, in my example above, the correct way to write the function would be: SUMIFS(C:C,B:B,">="&F2,B:B,"<="&F3)
Check out this site for examples: https://exceljet.net/formula/sum-if-date-is-greater-than
2. Section 3 - Filters - Entry box (cell) to enter a keyword/hashtag
a. Similar to the date range tip above, your function for filtering based on a keyword or hashtag will have to include the quotes and ampersand. Remember when we learned the COUNTIF function and the character to use to search PART of a cell? So if the data in cell B3 said "Today is a good day" and you wanted to count the cell if it had the word "good," your function would look like: COUNTIF(B3,"*good*"). You need the asterisks (stars *) around the word good to search for that one word in a cell. If you had the data below and I asked you to add the likes for any cells that had the keyword in cell F2, you might naturally think to write it like this: SUMIFS(C:C,B:B,F2). Or like this: SUMIFS(C:C,B:B,"*F2*"). Both will return 0.
In the first way, excel is looking for only the word good in a cell - which obviously isn't the case. The second way is no longer searching the keyword since we put F2 in quotes. It turned F2 into text and is now literally searching for the characters "F2" in the cell. Won't work. 
To get the function to search the keyword, you need to enclose the asterisks/stars in quotes and connect it to the keyword using an ampersand (&). Like this: SUMIFS(C:C,B:B,"*"&F2&"*")
This site does a good job explaining it: https://www.xelplus.com/excel-sum-partial-text-match/
Answered 1 days AfterMay 14, 2021

Solution

Kamal Barman answered on May 15 2021
17 Votes

Submit New Assignment

Copy and Paste Your Assignment Here