The function of the Count Function in Excel is to check if there is any text in a cell or if it is empty. But if we go to count conditionally, then it takes the use of the COUNTIF function. So today, I will discuss with you How to use the Excel COUNTIF function.
How to use the Excel COUNTIF function?
Formation of COUNT Function Or Syntax
= COUNT (range, range,….)
Where range can be B1: B12 or just one cells. For example, = COUNT (A1: B12) or COUNT (A1), its output is a number that will give you results by counting if the actually counted cells are not empty.
Formation of COUNTIF Function Or Syntax
Excel’s Countif function is used to calculate the number of available cells within a specific range of conditions. Its structure is as follows.
= COUNTIF (Range, Criteria)
The range can be one or more cells such as B1: B12, and Criteria is the subject to which you want to count the cells. For example, you may want to know how many numbers in cell B1: B12 are greater than 10 or how many times there is the same word like Math, etc.
The terms of Countif can be given a lot which we will know step by step. See the information in the table below. For example, there are several student names and topics.
So from the small data table, we will use count and countif functions in the B and C columns. I assumed that all those who sat for the exam got some or some number. And the house of the one who did not sit is empty.
Now for the above information, we can quickly determine how many tests have been done using the Count function. To do that, we will work with the information in the C column.
So in one of the empty cells, let’s say you want to see the result in cell D1, then press Enter with = count (C1: C12), you will see 11 come because one of the 12’s cells is empty. But if you want to know how many people got above 33 or how many are in math, you need a counteff function.
How to Use of COUNTIF function?
So from the data in the example above. We want to choose how many of them have a mark above 33. Many people are working on writing = countif (C1: C12,> 33), but it will not work!
The criteria for the countif function are to be given in text form. So they have to be included in the double quotation. Then the correct formula will be = countif (C1: C12, “> 33”)
So in the above example, we got an idea to find the number of students who got more than 33 with countif. If we want to know how many students have mathematics, we need to check the data B column above. Formula will be = countif (B1: B12, “Math”)
The two uses of Countif that we have seen above are examples of Exact Match, i.e., the answer can be found by directly matching the criteria in the data range. The function also works in Partial Match. But before that, I don’t know how to use greater than, less than, or equal. To in Excel count from the table below.
COUNTIF (greater than, less than, or equal to) Formula
| Conditions | Formula example | Description |
| Count if Greater then | =COUNTIF(C1:C2,”>33″) | If it is greater than 33, count the cells |
| Count if less then | =COUNTIF(C1:C2,”<33″) | If it is less than 33, count the cells |
| Count if equal to | =COUNTIF(C1:C2,”=33″) | If only 33, he counted the houses |
| Count if not equal to | =COUNTIF(C1:C2,”<>33″) | Count the houses whose value is not 33 |
| Count if greater than or equal to | =COUNTIF(C1:C2,”>=33″) | If it is 33 or bigger, count those houses |
| Count if less than or equal to | =COUNTIF(C1:C2,”<=33″) | If it is 33 or less, count those houses |
So We got the idea of giving Greater and Less.
COUNTIF’s Partial Match
Partial Match is not a complete match, but there are some matches to find out the number of such information. If you look at the names of the data we are working with within Excel, you will see that Mark exists a few times. So this time, we will find out how many times there is Mark.
We will look at the names, so the formula will be as follows.
= COUNTIF (A1: A12,”* Mark *”)
As you can see, we used two * signs in the formula. So if you put * in front, like * Mark. Then whatever is in front of the cells that are being searched, in the end. As if from Mark, in the same way with Mark *, whatever is behind. And by giving * on both sides, Mark will be counted wherever it is.
Use of COUNTIF function with date
We also have a lot of work to do with the date in the Excel sheet, and if you want to use the counteff function with that date, you can do the following.
The dates placed in the Excel datasheet should be entered, keeping the date format correct. From the above dates we can write for today = COUNTIF (B2: B12, TODAY ()) or = COUNTIF (B2: B12, ”26/26/2020 ″)).
The previous one or the next one can be removed and in that case = COUNTIF (B2: B12, ”<” & TODAY ()) or = COUNTIF (B2: B12, “>” & TODAY ()).
Count the number of empty cells and all cells
We can easily find out how many cell values there are using the COUNT function we saw from the beginning. This can be done with COUNTIF, and in that case, the formula is as follows, = COUNTIF (C2: C12,” <>”).
So you can find empty cells using = COUNTIF (C2: C12,” ”) and for this, there is a separate function in Excel: COUNTBLANK. Just enter the range here like = COUNTBLANK (C2: C12)
Use COUNTIF (C2: C12,” <>” &”*”) to find out the number of cells in the range.
Also Read: How to Highlight Active Columns and Rows in Excel?
Hope you learned How to use the Excel COUNTIF function? If you have any questions about this topic please share them with me by comment. Also, share on your social media profile.
