0:00 Here's an interesting problem, and it's sort of a data analysis problem, which is that we have, very often when we're dealing with sales, uh, selling items, we have a list of SKUs, items, we have dates that we sold, and we wanna know, though, like, maybe we put in a new advertisement on a certain day
0:20 , or a new promo that said, you know, buy one, get one free, headphones or buy headphones, or buy a keyboard, get headphones, or buy a laptop, get headphones.
0:27 Anything we want to do about dates. We may want to find out what the weekday is, and then figure out analysis from there.
0:37 For instance, we have some questions, like, how many days during the week do we sell laptops? Like, are we selling laptops every single day?
0:46 Are we selling mouses every day? Keyboards every day? Or is there a certain day of the week that we do sell them?
0:52 Maybe we wanna maximize that and do our promo then. or we want to start a new day if we have our say.
1:00 a not-selling-any-keyboards-or-laptops-on-a-Tuesday, we wanna create a promo that says, hey, on Tuesday, come in, get this, buy one, get one free, or something.
1:09 But also, we may wanna analyze our sales after we have implemented something, so we may want to filter down that data of which days of the week do we sell, uh, after, say, October 31st, or something, if there's some Halloween sale.
1:26 But also we wanna know, literally, which days of the week do we sell laptops. So, I'm going to show you a couple of, a couple of different ways to do this, but the first thing is that dates, we need to turn into weekdays, and there's a very simple text, uh, formula that we can use for this.
1:40 So we're gonna use text. The reason we use text is because we can change the number into a format, and you just have to know that four d's, lowercase d's, changes a date to the word of the weekday, which is what we as humans read.
1:57 We don't know that this is Friday, unless, we see the word Friday. We have to go and double-click here and see, oh, this is Friday, this is a Sunday.
2:09 We don't want to do that. We want to use this formula to just copy and paste all the way down, all of our data.
2:20 We now have, very quickly, the weekday in words we can read. And, instead of filtering this date for weekday, we now have a word that we can say, Oh, laptop.
2:30 Up and monitor, we're both sold on a Tuesday. We can do a really simple way to do this. Which days a week do we sell laptops?
2:40 We're going to equals filter. We're going to filter the range of D, the days that we actually do sell them.
2:47 But we're going to filter the A column is equal to laptop. And now we get a huge list of literally all of the days we're settings.
2:59 So we really only want to find it. Find out the unique days. So we wrap this with unique. And now we see that this is Friday, Sunday, Tuesday, Thursday, Wednesday, Saturday, and Monday.
3:10 So we are getting seven days a week we are selling laptops. But we can also figure out that number. We don't have to see the dates.
3:22 If we just want to say how many days of the week are we actually selling laptops. We can use count unique ifs.
3:31 We are going to count unique ifs because we want to do an if or a filter and then just count how many uniques there are.
3:37 So the range is going to be this weekday. The range, the criteria range is going to be that A equals.
3:46 And we don't need to do equals. We don't have to do the whole expression. We just need the range and then the criteria which is laptop.
3:55 If we put that in quotes, we get the number 7. So this is sort of a shortcut way. A of doing this unique filter and then we could wrap this with count, count A and we can get the number 7 here.
4:16 You can see this right above the little tool tip. It's going to get us the same answer, but we might want to display this and just get the number itself.
4:26 But the interesting thing is when we add this date, because we have to do something special. Which is we, one, we add a multiple condition, so we add another condition here, which is, we just keep putting in pairs of the criteria range and the criteria.
4:44 So we're going to say this, c to c, now in order to do greater than we put in quotes, greater than, ampersand, and we have to change this to date, to a date number.
4:57 Thank you. Get the year, so we're going to do year of c to c, we're going to do the month of c to c, and day of c to c.
5:18 Nope, that's wrong. We are already getting the date. We just need to put the date here that is October 31st, which is going to be 2024.
5:31 The month is 10, and the day is 31. And so now we see that we have four. Of course, if we want to know which four, we can use this unique filter, and we can just filter more.
6:07 And there we get the actual days of the week that we're selling laptops. So for some reason, right, we now know that in November we're only selling laptops on Friday, Sunday, Tuesday, and Thursday, whereas before we were selling them seven days a week, all days a week.
6:21 So perhaps, if our revenue goes up, we know that, oh, we did a promo on Friday, Sunday, Tuesday, and Thursday, and we know that, oh, the other three days of the week people were waiting, to buy it during a promo, so we can know the effect of our promos.
6:37 And I do want to point out at the end here that this unique filter is combining two formulas, which sometimes I find that this is a much more simple and useful way to find the answer.
6:51 We're going to also have to wrap this with count A if we want to count it, but we can do a lot of cool stuff with this unique filter, whereas with count uniques if.
7:01 We are only using one formula, but we have to remember that we have to use that particular formula, count unique ifs, and this syntax of adding ranges and then the criteria may be a little cumbersome for most, but this is a lot more efficient if you do remember the count uniques if.