 Hello and welcome to this session. This is Professor Farhad and this session we would look at the countless uses of the function COUNTIF in Excel. This topic is covered in a data analytics course in accounting or just simply a data analytics course. It could be used heavily in auditing and managerial accounting. As always I would like to remind you to connect with me on LinkedIn if you haven't done so. YouTube is where you would need to subscribe. I have 1,700 plus accounting, auditing, finance, tax and Excel tutorials. If you like my tutorials please like them, share them, subscribe to the channel, put them in playlists. If they benefit you it means they might benefit other people, share the wealth, connect with me on Instagram. On my website farhadlectures.com you will find additional resources such as the file I'm going to be using today to illustrate the concept. If you're studying for your CPA exam, CMA exam, underhold agent exam or any of your accounting courses you will find supplementary information to help you pass your exams especially your professional certification. You want to put the CPA behind you so you can focus on your career. My website, my resources can help you get those 10 to 15 extra points on your exam. So to illustrate the concept of the COUNTIF analysis I'm going to be using several cases just to illustrate the various concept of it. And what is the COUNTIF analysis? What's the first case we're going to be looking at? Let's assume we have a list of numbers and those could be a transaction number, dollar amount, sales order, purchase order, whatever they are but we have a list of things. They could be also names for that matter. It doesn't matter. We're going to see later that you could do it with names. And I want to see if any particular number of any particular name is counted more than once. It appeared in this list more than once which it should not appear more than once. For example, if you have a list of social securities for your employees it should appear only once in the payroll, not more than once. So how do I do so? Well, I do I can use the COUNTIF function and I'm going to click on the COUNTIF function. It's going to give me this dial box. In the dial box I always put my range. I'm not going to repeat this. Range is where am I looking. I'm looking in this data. And notice I'm going to be repeating this process repeatedly and I'm looking to see if the number 20 appears. Let's see first the number 6. If the number 6 appears more than once. And notice here the number 6 only appears once. I'm going to change the number 6 to number 20. And if I put the number 20 I say that the number 20 appearing twice. So if you have a large data and you know what you're looking for and you're looking to see if that number appears as a duplicate you could find out real quick if that number appears as a duplicate. Another version of this is to have the list but now what you do is you do the COUNTIF analysis. You would use the same function. However, for the the range is the same. The range is the same. For the criteria you would reference you would reference a cell rather than referencing a number. I'm going to be referencing E2 the cell E2 which is 20. Now all what I have to do is change the 20 into 10 and it's going to give me one count of 10. Change it to three. Give me one count of three. Change it to 20. Give me one count of 20. So notice you kid this is easier for managers. If they want to know how many asserting transaction amount or asserting employee inputting a transaction they could do it this way. Let's look at case three. Case three I'm going to be looking for any numbers in this list. How many times in this list the number was greater than a particular number. So let's assume I'm using the number seven. So what I'm going to do I'm going to start with count F. Select my range which is this list here and what I want I want the I'm going to put in quotation greater than or equal to I'm going to make it eight close quotation and click on okay. So seven times seven times I have seven instances in this list where the number is greater than eight. Now I can change this eight to 10. I can change this to 15. So basically this is great again this is great for managers if they are looking to monitor the process. Now rather than choosing a number in the list obviously case four is another version of case three and basically what I would do I would specify the number here seven okay or how many times is greater than eight seven times how many times greater than 15 it's four times. So notice case three and case four are the same just like case one and case two except what I'm doing. I am setting up in a cell and this is easier for a manager to do so. So it's easy to zoom in know what I'm looking for and move out just know you know how many times did the transaction appear greater than a certain number. Let's look at case five what I'm looking in case five is any number any how many times how many instances because I'm counting I did not have the number 20 so I have a list of transaction and I'm finding out how many times the number 20 did not appear in that list so anything but 20 okay. So notice here I will do count if let's do it gonna do count if count if count a function I'm gonna look in my list and in my criteria it's gonna be different than different than greater than or equal to so it's different and I'm gonna put this in quotation and the number 20 close quotation and it appears nine times now if I'm gonna look for the number 60 there's 10 counts of something other than the number 60 now how can you use this you could use it in any way you see fit. Let's look at case six let's assume I want to look for and not for one number I want to look for more than one number and how many times do they appear so let's start with this simple function so I could do count if and I want to know how many times the number three appear in the in this list well we did this the number three appear once now I want to know how many times the number three and the number seven so I get just put plus and I put the number seven so I'm looking for notice here I copy and pasted the formula I just put the number seven and now the number three and the number seven appear twice now I'm going to add it one more time I'm going to put the number 20 I want to see how many times the number 20 appear as well and now the number three seven and 20 appear four times in this list. Case seven another use of this count a formula if I want to see how many times the number appears that's higher than the average in a list okay or higher or lower for example let's assume I'm a manager in a in a retail store and I want to know that how many times that I have a return that are above the average return let's assume the average return is 25 dollars and once there is a return above average it's like a little bit suspicious it's a little bit on the high end why it's just if there's any any any fraud going on so how do I do this so I'm going to go with the count a function and I'm going to determine my range I'm looking in this range now what I want to do in my criteria I'm looking for the numbers that are greater than the average of that list so my criteria I'm going to have to put in quotation the number greater than the greater than and average and average and I want to look in this list so how many how many transactions that are typically greater than the average okay greater than the average and this is the formula I have four numbers here that are greater than the average now what is the average in this list real quick if you highlight your numbers in excel it's going to give you the average it's 17 dollars and 54 cent let's assume this is a sales return I have four returns in this list that are greater than the average which are 20 45 60 and 20 now I can easily identify them but in the real world it's going to be greater than I can do conditional format and if I want to and I can highlight them but the point is I want to real quick find out what is going on let's look at case eight I have a list of both numbers and number and letters in my name's a character a text character and I what I want to what I want to look for in this list is basically the word employees so if I want to look how many times the word employee appear in this list I will do count if look at the range and the criteria is the word employee exactly and the word employee appeared twice in my list here and here appear twice in my list now what happened if the word employee is listed as employees well that doesn't appear in my list so there's a way to solve this for case nine what I would do is I would go with the count if count if function and I'm going to select my range and now I'm going to change this a little bit I'm going to use the word employee and question mark so do I have do I have the word plural or is there something added to the word employee that's in this list and I notice here I have one instance what I have the word employee plus another character and it's employees notice this this name here is employees which is employee plus a character now what could happen sometime in those type of situation where you're looking for a particular employee or employees their name could be misspelled their name could be misspelled so what you need to do is or in the data rather than misspelled in the data their name could be attached to an account number for example employee one two three so it's it's employee but in the data the order social security is attached attached to that attached to that name so how do I know how many times did that name or did the word employee appear well case 10 under case 10 let's assume the employee has a number it's not plural it's like employee one two three uh or employee xyz so that it's an employee plus some character to it it's not only one letter it's many characters so what you do is you do the count if but now rather than an exclamation letter than a question mark so what I'm going to do now I'm looking for the word employee and if I put asterisk and I close the quotation it's going to look for the word employee in any character after that word so notice employee one two three the word employee employees and employees I have I have four instances and some databases what they do is they give you some time numbers before the word employee and numbers after now I'm looking on both ends so what I'm going to do I'm going to copy this formula it's the same formula paste it here the only difference now is I'm going to put the star on both the asterisk and employee at the beginning of employee and the at the end of employee click on okay and notice I have five instances employee one two three one two three employee employees one two employee two three four and employee five instances and the last case I'm going to show you and what if is let's assume I have a bunch of cells with both numbers and letters names and numbers and I want to know I just want to know how many times these cells contain text cells so basically I will do what if count if I select my range and and the criteria in quotation the asterisk and it's going to tell me how many time and these cells are only text cells five it means the remaining four are numbers if you like this recording please like it share it put it in playlist please connect with me on instagram um subscribe to the channel and visit what my website for additional resources especially if you are trying to pass your CPA exam good luck study hard and stay safe during those coronavirus days