How to use the countif function in excel

what is countif: countif function in excel is combination of count and if. If conditional statement which give us a result after comparing the condition and count statement count the cells. It is an inbuilt function of excel with predefined function outlay.

countif formula:

COUNTIF (range, criteria)

Range defines here cells and its value. Countif returns a numerical value after execution of command.
Criteria shows the condition according to that we access the data from spreadsheet.  criteria_range2, … criteria_range_n
Optional. It is the range of cells that you want to apply criteria2, … criteria_n .

We can also change it to make more formula using relational operator.

To use this excel countif example to understand it properly

countif formula

1) COUNTIF (B2:B8,”34″)

Counts the number of cells with marks ‘34’ (the value in B6) in cell B2:B8. The result is 1.

2) COUNTIF (B2:B8,”>32″)

Counts the number of cells with a value greater than 32 in cells From B2:B8.
And return the result 6.
3) COUNTIF (B2:B4,”<>”&B6)

ampersand ‘&’ acts as a connecter between relational operator and cell value. Countif functions select the value from B2 to B4 cells and compare it with B6 value. After comparison functions return result 1.

4) Countif(B2:B4,”>32″)+countif(B4:B8,”<85″)

 Blue and green selection box show that range which we mention in countif formula like B2:B4 representing by blue and second selection range B4 to B8.

This countif formula give us combine result of the two formula ,in first we are fetching data >=32 and in second data<85.The Result is 7.

We can create More countif formula according to requirement like:

COUNTIF(B2:B8,”=34″)                            

Countif count the value from cell range B2 to B8 equate the value with 34 .And give result=0.

=COUNTIF(A2:A8,”=Rahul”)

It is not necessary that we can only compare with numerical data .We can also compare with text. After processing the result come out 1.