Welcome, everybody. And in this video session we come up with count it's formula, one of the most powerful formula in case you want to establish control systems in the data that you're working with. And soon you'll understand how this is going to benefit you. The first instance that we're going to take is a list of hypothetical party names TCS, IBM and Zenit. I would want to know that the word TCS, the name TCS, how many times it is appearing in the given list. Now, basic count formula count a formula will only talk about occurrences of anything in the given things, let's say eight occurrences important, but what we want is a very specific criteria based count, for which let me begin writing the formula count.
It's I go to the effects button which is Insert Function button. And here we begin our journey. Criteria one, let me pick up the cell, the one in light yellow, that is TCS criteria range one, since it's a pair value, I would step inside criteria range one and pick up the array of values. So IE six to 830. I'm going to press f4. That's how it goes.
As I can see from the sample answer, it says three, which means as I press OK, I get three, three indicating TCS is appearing three number of times, as I change the name to IBM, it still shows me three, but if I right zealot, and that is appearing two times, so a very basic level count if is the formula which counts the number of occurrences in a given range for the specified criteria or criteria. In fact, a lot of people prefer to write the count if formula or just in to the data that they are working with. So in this case, I'm going to put the count if formula under the heading occurrences, click on effects. Go to criteria one, pick up the input criteria one, the one just left to it. And then in the criteria range one, pick up the entire range of value, after which, of course, you're going to fix this entire array.
I press OK. down. So for TCS three, I will double click to get this formula copy pasted, and you will notice again Zenit, it says two. In fact, if I just change the value to TCS from IBM to TCS, you notice the one which are appearing for a number of times has the value for against them. So this was the basic usage of count ifs formula. And in fact, if you want to just add one more layer of complexity, let me also write that any ft. Any ft. to any STP mint. That Zenit, the companies in it through any SP mod, how much how many times it has given me the amount.
So I want to find out in the entire list how many Zenit company exists, not only that, amongst Zenit, the one which shows NFP, how many are there So what I'm going to do is I'm going to write the count is formula again, click on effects, open up all the boxes because I would need two pairs of criteria this time. Two criteria one, doesn't it. Criteria two. Notice I'm stepping inside the fourth box that says criteria to and, and using the cell Naft. Now once I've done so, the sample preview choose means that it against the second box. So I step inside criteria range one and choose the range of values from a six to a 13 pressing f4 to lock it.
I step inside the third box which says criteria range two. Now what was my criteria two it was any FP so let me choose same height from the column F. I can see the height the same is 63 f six to 830. I'm going to lock that okay. So it says any any ft appearing only one time and if I write TCS in EFT that is appealing tee times you can vouch for accuracy just by looking at the data and here it goes three times. So this was the two different application of count if formula. And to close the entire session.
Let me just quickly show you how instance number is formulated and shown on the theme was instance number. In a long list, I want to find out that this is appears first time, this is your second time this year appears third time. for IBM, it's going to be first time and second time towards the last end. So this we will adopt a similar strategy as what we have also adopted for some ifs formula to be right countifs. Click on effects. Go to criteria one, pick up this Yes.
But when you go to criteria range one, you pick up the same cell but add a colon to it automatically a six appears once again and make sure you lock only the starting point of this range. So eventually, the range Going to increase it should start with a six and is going to increase to 8789. As you copy paste it down. So when I do that, notice what you get for TCS. In fact, let me hide the other columns which are not going to be of any help right now. So here you go.
This is appealing first time, second time, third time and fourth time. I've been appearing first time and second time. You might be thinking, where would I need this? Well, I've seen people, they want unique ID codes to be generated from repeating IDs. So in this case, if I combine TCS with that of this number that we got this on our stream, you will be able to populate unique IDs based on column A and column C values TCS. One Two UCS three four iv 12012.
So this we often refer as a differential cumulative count In simple terms, it is the instance number. So there you go. That was a super trick from the counters formula