ADITYA GREENS
  • Home
  • My Solar Guide
  • Solar Feasibility Spreadsheet
  • E- Products
  • About
  • Tools
  • Privacy
  • Get 3 quotes Free
  • Solar Installers

How to use the COUNTIF function in Excel?

28/11/2018

2 Comments

 
​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 function 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
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 that it should be > 33%
  • Result: 14 (There are 14 such students who scored more than 33% marks in the examination)

COUNTIF function when the text is given

​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
(*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 a 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
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 with start 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:
End text matching

Find the Blank and non-blank cells using the COUNTIF formula

​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:
count non blank cells
And the blank cells are as follows:
Count blank cells

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.
2 Comments
Charu Singh
28/11/2018 12:17:03 pm

Very well explained.

Reply
Yash Kumar link
29/11/2018 09:13:06 pm

Thank you for reading the post!

Reply



Leave a Reply.

    Archives

    December 2018
    November 2018

    Categories

    All

    RSS Feed

  • Residential Solar Feasibility Spreadsheet now available in
  • India
  • USA
  • South Africa
  • Brazil
  • Jordan
  • ​UAE
  • Installers
  • Get 3 quotes FREE!​
  • E-Book: How to design the solar power system by yourself?
Copyright @ Adityagreens. All rights reserved
  • Home
  • My Solar Guide
  • Solar Feasibility Spreadsheet
  • E- Products
  • About
  • Tools
  • Privacy
  • Get 3 quotes Free
  • Solar Installers