MEND MY LADDER
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure

How to use COUNTIF function in Excel?

1/4/2019

1 Comment

 
COUNTIF function in EXCEL
The COUNT IF function in Excel is used to count those cells within the given range with the specific condition. In this article, we will see the usefulness and the versatility of the COUNTIF function.

The syntax of the function is as follows:
COUNTIF (range, criterion)
  • The range is the number of cells you specify in your worksheet and this can be specified as A1:A10 or B1:B20.
  • The criterion is the condition which enables the Excel to count those cells, out of the range of cells, which meet your requirement. For example, if I set the criterion to count those cells which are greater than 25, the Excel will select these specific cells, count them and will produce the result.
​
Let us explore this function with some examples having different criterion:
​

COUNTIF with numbers


Below is the list of 20 students with their marks in the percentage.
​
  • The teacher is assigned the job to count the students who scored > 33% marks and thus calculate the passing percentage.

She can very easily know the number of students passed in the examination using the COUNTIF function and can calculate the passing percentage of the students.

COUNTIF with numbers
COUNTIF function with numbers

The passing percentage of the students is 14/20 * 100 = 70%

Let me remind you the working:
  • =COUNTIF (C2:C21,”>33%”)
  • The Range is from C2 to C21 and 
  • The criterion is > 33%
  • Result: 14 (There are 14 such students who scored more than 33% marks in the examination)

COUNTIF with text


Another simple example to match the text and count the number of times it occurred in the selected range of the texts.

Below is the list of the football winners across the world in the last 60 years.

Let us say that I want to know the number of times team Brazil has won the world cup title in the last 60 years.

I can do this very easily using the COUNTIF function which is illustrated in the screenshot shown below:

COUNTIF with text
COUNTIF with Text
(*The criteria are case insensitive that is whether you write “brazil” or “Brazil”, the formula will produce the same result)

The working of the formula is as follows:
  • =COUNTIF (B2:B17,”Brazil”)
  • Range is B2:B17
  • Criterion is “Brazil”
  • Result: 5 (Excel will count the number of time "Brazil" occurs in the given range and produce the result)
​

COUNTIF with partial match


This function can be used to count the partial match of the characters out of the given range.

​Suppose you want to know that how many students are there in the class with surname Gupta, irrespective of their different initial name, from the list of the students given below:

COUNTIF with partial match
COUNTIF function with partial match
The asterisk (*) sign in the formula is used to find and count those cells with any sequence with the text “Gupta”.

Again, if you want to know the names that start with alphabet "A" and it does not matter how many other letters it contains, then use the following formula:
COUNTIF for letter match
Using COUNTIF function for first letter match

End text matching:
​

Similarly, if I say that I want to know the students’ name end with “ma” irrespective of the number of the letters at the beginning of “ma”, we can use the following formula:
COUNTIF for end text matching
Using COUNTIF for end text matching


Finding Blank ans Non-blank cells


This function can be used to find the blank and the non-blank cells in the given range.

Suppose the station master wants to know the status of the number of trains with no remarks about their whereabouts.

​We can help him using the COUNTIF function which is illustrated in the following example:
non-blank cells using COUNTIF
Finding non-blank cells using COUNTIF function

And the blank cells are as follows:
Blank cells using COUNTIF
Finding blank cells using COUNTIF function

Conclusion


By now you must have seen and understood the versatility and the usefulness of the COUNTIF function.

​It is one of the simplest functions which meets your multiple requirements.
1 Comment
https://uk.eliteassignmenthelp.com/ link
24/11/2022 10:05:50 am

The COUNTIF function is the most popular function in Excel. If you are not familiar with COUNTIF, it is used to count cells that meet a specified criteria. In this article, you will learn how to use the Excel COUNTIF function step by step.

Reply



Leave a Reply.


    Author

    Hi! I write posts on growing your business


    Categories

    All
    Grow Your Business


    Archives

    August 2021
    July 2021
    April 2021
    March 2021
    February 2021
    May 2019
    April 2019
    March 2019
    February 2019


    Categories

    All
    Grow Your Business


    RSS Feed

Copyright Mend My Ladder @ 2021
Proudly Powered by Weebly
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure