Complete Tutorial How to use Countif Function in excel

Complete Tutorial How to use Countif Function in excel, Here we learn completely about countif with examples, advanced countif function in excel with Excel Free Learn.

Why We use Countif Function:- If we talk about counting on a condition, then we use the Countif function in excel. Here it is only for a single condition.

Syntax:- Countif(range,criteria)

Parameters of Arguments:-

Range:- Range means where our criteria match, we are able to find our criteria. Groups of cells from which you want to count.

Criteria:- Criteria means a condition which we can use and find them in a Range.

Where is Countif formula in excel:- Easily you can use this excel formula by clicking on cell =countif() 

In this bracket you select range and criteria. Here criteria means a condition.

On the other hand, I have a option for countif formula that is 

Formula Menu Bar > Function Library Group > More Functions > Statistical > scroll and select Countif After that you show a Pop up window Then fill all Parameters of arguments like range, criteria.

In order to understand Countif in a good way, we understand it with different examples.

Count if function:-

Example 1:- If we have given salary to the staff and we want to know how many staff we have paid then we will use the Countif function in excel. If we want to know how many staff work in a selected department then we also use countif Function.

how many staff in the department who get salary

Here we are using employee data for counting how many staff in the department who get salary. Let’s See image.

In Cell H6 we are using =COUNTIF($B$5:$B$14,F6)

And then Ctrl + D 

We have to range fix by $ otherwise our answer is wrong. This is called absolute referencing (Column and row does not change when copy and move)

If you want to learn Sorting in excel you can learn easily.

Now we are sharing Second image where you can see

Count Example 2, 3, 4

Count if with Multiple Creteria:-

Example 2:- If we want to see any two or more conditions on the same cell, how many times they are coming at the given range, then we have to use Count if with the sum function in excel.

We are using this formula with words in Cell I3, Please note when you use the formula that your criteria in { } this bracket otherwise your formula is wrong. If you want to learn you have to practice not only copy paste. 

=SUM(COUNTIF($B$6:$B$14,{"Full Time","Work from home"}))

count if cell contains:-

Example 3:- Count Employee per Criteria, In this example we are discussing Status. If we want to know how many staff in which status at this time according to excel data. We are using formulas in H6 Cell.

=COUNTIF($B$6:$B$14,G6)

And then drop formula or copy paste as you wish. We can also use the “On Leave” instead of the G6 cell.

=COUNTIF($B$6:$B$14,”On Leave”)

You have to pay more attention to this. Your spelling is correct. 

Count if greater than and less than:-

Example 4:- In this example we are discussing Salary. How many staff which salary above or equal to 80000, below or equal to 80000 and equals to 80000.

For that we can use signs in this formulas like 

Greater than equal to “>=”

Less than equal to “<=”

Equals to “=”

Greater than “>”

Less than “<”

This formula in cell H14,H15,H16 respectively

=COUNTIF($C$6:$C$14,">="&G14)

=COUNTIF($C$6:$C$14,">="&G15)

=COUNTIF($C$6:$C$14,">="&G16)

When we are using concatenate signs with & sign like ">="&G14

You should know Tips & tricks:-

1. When using excel formula keep in mind do not copy and paste if you want to learn formulas in excel in a short time. 

1. Practice, Practice and more Practice use this formula in your life if you want to learn anything.

Conclusion:- 

We hope this topic Complete Tutorial How to use Countif Function in excel you have come to know how to use Countif unction, we want to try to understand you with more and more examples, so we will keep adding examples to it so that when you apply the formula, there will be no Confusion Stay tuned and you can use Countif at an advanced level.