MS Excel spreadsheet was developed by Microsoft for different operating systems such as Windows, Android, iOS and MacOS. The spreadsheet carries out calculations through the implementation of various formulas and functions.
One of the most important functions in Excel is COUNTIF. The purpose of this particular function is counting cells with user-specified conditions. Here, we are concerned with how to count cells in Excel that are not blank. As mentioned earlier, the purpose of Excel COUNTIF is to count cells within a specific range, that meet the defined criteria or conditions.
First, we will talk about the syntax and usage of COUNTIF function in Excel.
COUNTIF Function: Syntax and Usage
The Excel function, COUNTIF, is a built-in feature that gets recognized as a function for preparing statistical figures. As a function, COUNTIF is entered as a part of a different formula in respective cells of a worksheet.
For example, one can find out exactly how many cells in a worksheet contain a number greater than or less than the number the user specifies—through a COUNTIF formula. Among other things, this can also be used for those cells that have a particular word or a cell starting with a specific letter.
Syntax of the COUNTIF function is:
COUNTIF (range, criteria)
So, we see that there are just two variables, range and criteria, which are required to find out the exact result.
Let us see what the two variables are all about, one by one.
- Range: Range is basically defined as one or more cells which are to be counted. One can insert the proposed range in a formula, like the one usually employed in Excel, for example, A1: A10.
- Criteria: Criteria tends to define a specific condition that lets the function know which cells should be counted. This could be any number, string of text, or reference to a particular cell or expression. For example, a particular criterion can be used as in this list: “20”, A3, “>=20”, or “some text”.
Now through a simple example, let’s describe the function better. Suppose we want to know how many times cricket player, Sachin Tendulkar, was adjudged “player of the year”—as compared to other renowned players of international cricket during the same 10-year time frame.
So, to find out the instances when Sachin had been given the player of the year award, we consider the 10-year period of 1996-2005. We show this through a diagrammatic representation.
From the above list, we conclude that over the period of 10 years from 1996 till 2005, Sachin Tendulkar was awarded as the player of the year more than once. Along with him, players like Mark Waugh, Saeed Anwar, Brian Lara, and Ricky Ponting have also been recipients of this award.
So, to understand exactly how many occasions Sachin Tendulkar received the award, we would simply use the formula of COUNTIF.
The formula to be used in the formula bar is:
fx=COUNTIF (B2: B11, “Sachin Tendulkar”)
This will tell you that Master Blaster, Sachin Tendulkar, was adjudged the player of the year exactly 4 times from the period of 1996 to 2005.
This shows us how the COUNTIF function works to achieve the counting of cells which are filled with text, and simultaneously find out a specific data.
There are various other ways to count cells filled with text, numbers, wildcard characters, and specifically for non-blank cells.
COUNTIF Formula for Text and Numbers (Exact Match)
We discussed the COUNTIF function, which counted the text values matching specific criteria, using an example through which we had found out the exact number of occasions when Sachin Tendulkar was adjudged player of the year in a period of 10 years from 1996 to 2005.
The exact string of text in the cell was:
=COUNTIF (B2: B11, “Sachin Tendulkar”)
Now, without typing any particular text, we can only refer to the cell which contains that particular word and still get exactly the same results.
=COUNTIF (B1:B10, B5)
Similarly, the COUNTIF formulas apply for numbers as well. It can be depicted using an example:
Now, suppose we have to get the result on how many occasions does quantity 5 appear. The formula would be:
=COUNTIF (B2: B8, 5)
It perfectly counts the cells and gives the result 3 in cell number C2.
COUNTIF Formulas with Wildcard Characters (Partial Match)
Suppose Excel data consists of different variations of keywords which are to be counted. This can be done by using a wildcard character which counts all the cells containing a specific word, a combination of letters, or a phrase as a part of the cell’s contents.
For instance, let’s say that the data includes a range of tasks assigned to different people. You would like to know the number of tasks assigned to a specific person, say Michael Wood.
In this list, the name is written in three different ways. So here we enter “*Wood*” as the search criteria:
=COUNTIF (B2: B6, “*Wood*”)
And as you can see, the result is displayed in C2 as 3.
In the above scenario, an Asterisk (*) has been applied to the word in order to find the cells having any of this sequence of characters, both leading and trailing. In case a requirement arises to tally any single character, then we need to enter a question mark.
Count Cells Which Are Beginning or Ending with Certain Characters
Suppose you want to know the exact number of cells that start or end with certain text, irrespective of the different types of characters the cells possess. For example, say you want to find the number of cells beginning with “Mr” from the above set of data, then the formula can be written as:
=COUNTIF (A2: A6, “Mr*”)
Now if you want to find the number of cells starting or ending with particular letters and containing the exact number of characters, then Excel COUNTIF function is used along with a question mark character in the criteria.
For instance, if the number of cells ending with “od” and containing only 4 characters in cells B2 through B6 (including spaces), need to be counted, then the formula would be:
=COUNTIF (B2: B6, “?? od”)
Excel COUNTIF for Non-Blank Cells
Now, with the help of some formula examples, we would see how the function COUNTIF is used in Excel for counting the number of cells that are not blank in a particular range.
The formula generally used is:
=COUNTIF (range, “ * ”)
But there is a problem in this formula. The problem is that the above-mentioned formula only counts those cells having any sort of text values and not those with dates and numbers.
Hence, the cells with dates and numbers would be considered as cells having no value and wouldn’t be counted. But there is one single formula to count all cells with different value types, containing some write-ups, and mention of certain dates and figures in a particular range.
The formula goes like this:
=COUNTIF (range, “ “&” ”)
So, we can clearly see how Excel COUNTIF is required to carry out cell counting when cells are not blank—but contain texts, numbers, and dates. It all can be done through the formulas mentioned above.