The Microsoft Office Suite has a wide range of efficient and relevant software that is essential for any business to run properly and one such application in the Suite is Microsoft Excel. Excel is the widely preferred data business analysis and reporting tool that almost every firm uses nowadays. Because of its easy to use features and host of functions complementing every need that the Excel consultant and causal business user might have, Excel is not only preferred in business offices but also in schools, colleges. One such function that it possesses is the IRR function for example. Here, in this free Excel help article we will discuss in detail about the Microsoft Excel COUNTIF function.
We have over 100 how to articles on Microsoft Excel!
It is often the case that one wishes to know beforehand the number of items that are present in an array or a set that are to be computed on. For such cases Excel has the COUNTIF function at its disposal. The COUNTIF function when provided with a set of cells or fields checks each and every one of the cells individually and notifies the user with the number of actual numerical values present in them. That means it ignores any non-numerical presence in the cells like texts, symbols, representations etc. Not only that, its specialty is that it only computes with the criteria specified by the user, in mind. The syntax for the function is COUNTIF (range, criteria). Here the range specifies the whole range of numbers, parameters, arguments or whatever we are putting in as the input to the function for computing. It can also be array members or cell references as well in the list. The criteria field actually takes in the criterion for which the range is to be searched for and if some members meet the requirements then only they are counted in as the admissible values.
To better understand the concept and usage behind the function we have provided below a host of examples that will be very helpful for beginners as well as a refresh for those experienced Excel consultants.
Suppose, the user wants to know all the numbers in a list which are greater than 5, so in order to know that he has to call the COUNTIF like this: COUNTIF(1, 4, 6, 8, 10, 11, 20,”>5”). Then the function automatically takes “>5” as the criterion on which it calculates the numbers which are to be counted upon and here since the last 5 numbers are greater than 5 so it returns 5 only. Similarly, we can compute for array cells as well, placing them in the range field and criteria fields. For example, if we write COUNTIF (A3: A10, D1) then it will go through all the elements in the array column A starting from 3 and ending in 10 and will look for those which meet the criteria mentioned in D1. Again, we can even pass it like COUNTIF (A3:A10, “>=50) and it will then even work just fine.
COUNTIF not only can take up proper numerical values but also is programmed to take in dates and numerical representations as well. If the text is a representation of a number, like “seven”, then it does identify it and takes it up but it does not do the same in any other case, not even for error values or empty cells that are present in the argument. The COUNTIF function can even take wildcard characters such as the question mark (?) and the asterisk mark (*) for detecting a character and a string of characters respectively when put in the criteria field explicitly. However if an actual “?” or “*” is to be found then a tilde (~) character is to be placed in front of it.
However if one wants to get rid of all this fuss associated with criterions and all then he can easily use the COUNT function which counts all the numbers that are present in a specific field or argument set and don’t need any criteria to be mentioned by the Excel consultant or business user. The COUNTA function can even take logical values like Boolean values of TRUE and FALSE to be passed on to as its argument. For making note of the cells which are empty the COUNTBLANK function is used.
The COUNTIF function is one of the most popular Excel functions around and is compatible with Excel 2000, 2003, 2007, 2008, 2010, and 2011.