 Hello everyone and welcome back to another video. So this video is going to be focusing on a recent question I received on another video and that question revolved around how can you count the number of months that appear in a range? So to sort of be more specific with this question, you can see we have a list of dates and of course those dates are varying from being the first of the month, the fifth, the twentieth, yeah so varying days within that month as well and The question was asking what formula would allow you to be able to count how many times the month of February For our good sake appeared in this range. So we can see the month of February has got a Three entries or three dates the first the fifth and the twentieth So the result we want to return is the number three Alternatively, if we're looking for how many times did the month of June appear then we can see the month of June appears here twice It's all in hover order. So it's quite clear to see that one We can see it appears twice. So the result we'll be looking for there is the number two So what we'll do is we'll just jump straight into it and show you the formula and try and explain the best We can obviously how the formula works. So what we'll be using this time is a function called sum product And what that allows us to do is obviously Within that sum product apply the logic we require to count the months and then some product will do the math or the The sum to obviously give us the return of that count total So what might be easier is if we just jump ahead and is a false calculation there already if we just jump ahead and enter the Formula then you'll have what you need and then we'll try and talk through them Obviously that formula and how it works So as mentioned, we'll be using some products. So we go equals some products and Then the first part we need to do and again, we'll explain this towards the end is we've got a double dash Followed by a couple of open brackets and then this is the part will probably see most logical Or most clear shall I say so we're gonna use them now that month function so we're gonna use that and then open a brackets and Our dates that we're interested in go from row eight down to row 17 So we're just gonna be very specific and select that range. I'm also going to just lock that range as well by using F4 We'll close brackets So at this point we can see that this is the test obviously we're looking for months So we want to know the month of this range and obviously what some product is going to now To do is I obviously iterate through each of those rows one at a time and the first thing we want to test for let's say We'll go for the month of May. So the first one is my eyes drew to there is in row 12. So we're going to do where month equals 5 so that the number that represents may be in number five and Then we're going to do a few closed brackets as well And then probably worth me entering this make sure it works just before I start explaining the formula Correctly so we can see we've got the result of one So to quickly test that and make sure our formula is working. Let's go for the example of June So we can see that rose 13 and 14 here. So all I'm going to do is go into the formula and change this value to the number six Hit enter and you can see we're now got the result of two. Let's just do one more quick test So we've got the month of February here. Let's change that to the number two for February Yet, we're getting three and then a final test. Let's just change this one here to another date. Let's go for a random date 20 of February 2021 yet, you can see as that day was entered our formula has also captured that as well at this point It's also worth mentioning that obviously everything in here is fairly static including the month We're trying to refer to so what I could probably do up here. Let's say let's go for the month April We could change this so actually rather than hard coding it in we just refer to this up here Hit enter and yet we can see there's nothing for April But if we go for July, so we change this to the number of seven then yet you can see it's now updated as well So really quick on the fly there You can obviously hard code your month into here else if you want to obviously have it dynamic So you can literally keep changing as I was doing in my examples there Then obviously you can refer that to a certain range that contains your month number So to try and explain this function a bit more detail So ultimately what we're doing or not the crucial part but the key part here is we're ultimately saying we're interested in obviously the month So what it will do is it will look at this range and for each row it will turn what that month number is If you're not familiar with the month Function all it simply does and I could show you now here quickly is if you go equal month and Then step on these dates you can see it's just going to return the integer value For the corresponding month of the date we've selected so obviously that's what you can what's happening within the formula So it's obviously for each of the rows it's going to return what that month number is And our criteria is it must equal in this example in number seven And then what our sum product allows us to do is to go through it sort of say record each of those Instances where the criteria is matched within this array and then obviously it will then do a total count For all of those in there as well. What gives us obviously the result of three One of the questions we often get around some product is why do you have the double dash at the front here and Simply what it is doing is it's converting a list of boolean so it's like true or false values To zeros and ones. So what do I mean by that? So ultimately what's happening here first is our logic is saying okay for each row Is the month equal to number seven and obviously what that's going to do is return a true or false if it's true Then obviously great any such criteria if it's false then we ignore it and move on But what we want to do is we don't want to return a true or true or false We want that true or false to be converted to a number So obviously number one or a zero and then what happens is once we've got a list Actually probably another another visual representation might be probably useful in this scenario So we're looking for the month of July So what our formula is doing by going through each row and identifying if this is true It's first going to go through and say are all these true or false so for the majority of them it's going to be false and I can copy that down into this Obviously these bottom ones are going to be true Because obviously they meet the month quite here of July So the first part that happens for this statement here is it's basically going down all of our rows and say okay Tell me true or false if that that date is equal to or the month of that day is equal To the one we require and it'll basically go down and create an array of all these values for true and false Because obviously we can't really work with true or false in this scenario What we need to do is convert all these truths and falses to ones and zeros So again in essence what's going to happen here is the next part by doing this Double negative here is it's going to go and convert all these to I don't know what zero percent there But let's go with it zero zero double zero If it helps if I put the zeros in and then this would be a one one one Let's change that back to a number. What's now going to mean? It's wrong as well. So let's go zero zero That's what you get for doing stuff on the fly Get rid of that and just get rid of all those as well So we can then see we get there eventually What's happened now? So all our truth and falses have been converted to ones and zeros and then all our sum product needs to do is now some All of these results within that array when it does a sum of this obviously is they're going to give us if you Probably see that down the bottom of my screen you can give us the result of the number three what we've returned here and In a nutshell I say but turned out to be quite a long-winded example of how this function works But in the most shortest terms if you're trying to count the number of times a month appears within a range Then this is the formula that you need to achieve that as well So if you did enjoy that video, please don't forget to smash the like button It's not only greatly appreciated by me because it shows the content that you potentially like to see more of But it also helps that all important YouTube algorithm for ensuring that more people find these videos And if you haven't subscribed to the channel already or if this is your first time checking out one of the videos Don't forget to hit that subscribe button that way you'll be notified of more videos as and when they come out So thank you very much for watching and we'll see you in the next video