COUNTIF in Microsoft Excel

If we need to count the number of words in a range, in Microsoft Excel 2010 and 2013, we have to use the  COUNTIF formula.
COUNTIF function is used for count the text or numbers on the basis of multiple criteria.

The Syntax of COUNTIF: – =COUNTIF(Range,Criteria)

We can use this function with the multiple ways, you can find the below mentioned arguments, with the formula types and explanation.

img1

Note: – We can count the duplicate text or number in row and columns.

Let’s take an example and understand how we can use the COUNTIF function to multiple ways.

We have student’s score data. Column A contains Student’s name, column B contains Subjects and column C contains Scores %age.

img2

We need to count the cells in Subject column which are contains the end words with “ce”.

To Count the cells on the basis on end letter follow below given steps:-

  • Select the cell D15 and write the formula.
  • =COUNTIF(B2:B13,”*ce”), press enter.
  • The function will return 4, it means 4 cells are there in which word end with ce.

img3

We count the cells which contains the text in Subjects column.

 To Count the cells which are contains text follow below given steps:-

  • Select the cell D16 and write the formula.
  • =COUNTIF(B2:B13,”*”), press enter.
  • The function will return 12, it means range B2:B13 contains only text.

img4

We count the cells which donot contains text in Score column.

To Count the cells which are not contains the text follow below given steps:-

  • Select the cell D17 and write the formula.
  • =COUNTIF(D2:D13,”<>”&”*”), press enter.
  • The function will return 12, it means score columns not contains text.

img5

We count the duplicity in the Student’s Name column.

Count the cells which are duplicate in a range follow below given steps:-

  • Select the cell E2 and write the formula.
  • =COUNTIF($B$2:$B$13,B2), press enter.
  • The function will return 2, it means Aaron are repeating 2 times in Student’s Name column.
  • Copy the same formula by pressing the key Ctrl+C and paste in the range E3:E13 by pressing the key Ctrl+V.

img6

This is how we can use COUNTIF function in Excel to count the duplicity in the data.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube