Hello, everybody. Welcome back to the second dashboard. In this video, we will see how to create a dynamic formula, which will count from the number of employees getting appointed in different countries for the respective year provided by the user. So to count the number of employees, it is in short to count the number of times the respective countries are getting repeated. For every employee, one country will be repeated. So the simple formula that we are going to use is count if so we'll use equals to count if count is is simple formula, we just need to specify the range and the criteria range is something which we already have.
And we just have to go to the reference 2012 sheet and we have to select the entire country column. Once done, we just have to freeze it, comma and then come back to the actual sheet. Where a formula is Once done, we simply have to select the current country which is in my case India and then remove the extra reference provided by the selection. And once done, close the bracket and enter and there it is. As we already know, as the reference changes, the answer should also change just like in the earlier videos, we saw the indirect function. So we'll just group indirect function into the existing formula.
So indirect, open the quotation. And I'll just cover the quotation as well remove 2012 so I'll just use an ampersand connect to 2012 as the cell reference is that is user input here, which was already given as a name put in another ampersand with double quotations and close the quotation. So now close the bracket for indirect. Now this entire reference will become the reference for the indirect form. formula, which in turn will take the countif function to the particular reference. As of now it is 2012 sheet in the future, it may change to 2013, and so on.
So let's see how the output looks. So drag it down, I get the relevant answer. So let's check for the status for 2013. Isn't that fascinating. And if you want to get the total numbers, you can use the formula sum. So I'll just do that.
We can use the sum and simply select all the records and that will show us that in the year 2013. In total, there are over 7000 people employed in 2012 the total number was 7061. Now let's check out some other years 2013. Now the answer didn't get updated. That means there's a flaw so let's understand the flaw. If we come down to our 2012 sheet and check out the data, we could see the last record is 7062 Records.
But if we go to another sheet, it's not the same. It's 12,000 that means the reference that we were taking is limited. Now, here the best option shall be rather than selecting just a reference, that is simply just h two to H and we can simply select the entire column as a reference. Now, just remove the reference and that should help me to get the relevant answer and there it is. Now if I change it to 2014 that should update the entire list accordingly. Now I can assure in 2014 the number of records are the same that is 12,046.
But as we can see now if if it is for 2012, I do not get the relevant answer. That's The changes that I have to make in the other formula as well, which we shall be doing when we get back in the next video. Now in the next video we will see how to calculate the percentages. Till then keep practicing. See you in the next video. Thank you