WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » COUNTIF Examples

COUNTIF Examples

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

COUNTIF Examples in Excel

Example of Countif Function is =Countif(A:A,”Anand”) this will give us the count the number of times Anand is present in the cell range A:A, =countif(A1:B7,”Excel”) this will count the number of times excel word appears in the cell range A1 to B7.

Example #1

You can download this COUNTIF Examples Excel Template here – COUNTIF Examples Excel Template

Now take a look at the simple example of counting numbers from the large list. Take an example of the below data.

data to count numbers

From the above list, I want to count how many times 15 number is there in the list.

  • Let’s open the COUNTIF function.

countif function

  • The first criterion is the range. This is our list of numbers, so select the range as A2 to A10.

excel countif example 1.3

  • The next and final part is to tell the criteria. Criteria are nothing but what we need to count. Mention 15 as the criteria.

excel countif example 1.4

Note: if the criteria are text value, then we need to mention in double-quotes; if the criteria are numerical value, then we need not mention in double-quotes.

  • Click Enter, and we will get the answer.

excel countif example 1.5

So the total count of the number 15 is 2.

Example #2

Now take a look at the example of counting text values. Consider the below data for the example.

data to count text values

  • From this list, we need to count the words Government. Let’s apply the COUNTIF function to get the total.

countif function

  • Since we are counting text value here, we need to supply the criteria in double-quotes. Mention the criteria as “Government.”

excel countif example 2.3

  • Click Enter, and we will get the answer.

excel countif example 2.4

There is a totally six-time Government word appearing in the range A2 to A11.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example #3

In the above example, we have counted the word Government. What if we need to count the other words from the range? Applying COUNTIF to all the criteria does not make any sense.

Let’s create a drop-down list of all the three values in the range, i.e., Government, Private, Midmarket.

  • Select a cell where you want to create a drop-down list. Go to the Data tab; select data validation.

how to select data validation

  • After selecting Data validation, the following window will open.

data validation window

  • In the Allow section, choose a List and in the Source type Government, Private, Midmarket.

Source type Government

  • Then click ok, and you get a drop-down list at a selected cell.

excel countif example 2.8

  • After creating the drop-down list, apply the formula but don’t write the criteria in words; rather, give a link to the drop-down cell. Refer below image for your reference.

excel countif example 2.9

I had given a link to the cell C2 for criteria; the cell C2 holds the drop-down list of all the items. Whenever you change the value in the drop-down cell (C2), COUNTIF gives the count of the selected value.

Example #4

At the beginning itself, I told you that COUNTIF could count only one item at a time. But we can apply some other logic to make it count multiple values as well. Mainly there are two techniques to do this.

Technique 1

We can add two COUNTIF functions one after the other to get the count of two or more values. For example, take a look at the below data.

excel countif example 3.1

  • I want to count Government as well as Private from the list. Firstly, as usual, apply the COUNTIF function for counting Government words as you have learned in the previous example.

count of government and private

This is easy now because we have learned in the previous example. Here you need to use your brain to the next level.

  • Firstly understand the question we are answering, the question is we need the total count of Government & Private. After the first formula, add the plus (+) symbol and start one more COUNTIF function and count Private word in this COUNTIF function.

excel countif example 3.3

  • Click Enter, and you will get the answer.

excel countif example 3.4

Wow!!! We got the value. Yes, first, COUNTIF returns the count of the word Government, and the second COUNTIF returns the count of the word Private. Since we put the plus (+) symbol in between these two COUNTIF functions, it will give us the total of numbers count given by these two COUNTIF functions.

Technique 2:

The second technique does not require two COUNTIF functions to pass. We can count these two values in a single COUNTIF itself.

  • As usual, you open the COUNTIF function.

excel countif example 3.5

  • In the criteria, the argument opens a curly bracket and supply two criteria instead of regular one criteria.

excel countif example 3.6

  • Now here, COUNTIF alone cannot give me the result here. Since we are adding up two values here, we need to apply the SUM function before the COUNTIF function.

excel countif example 3.7

  • Now hit enter this will give you the total count of Government & Private.

excel countif example 3.8

Example #5

We can also use COUNTIF with operator symbols. For example, in a list of numbers, what if you want to count numbers that are greater than a certain number.

  • Consider the below data for an example.

excel countif example 4.1

  • In order to count the numbers which are greater than 20, open the COUTNIF function and select the range first.

excel countif example 4.2

  • In the criteria, firstly mention the greater than symbol (>) in double-quotes.

greater than symbol

  • Now supply the criteria number with ampersand (&) symbol.

criteria number with ampersand (&) symbol

  • We have got the total count of numbers which are greater than 20.

count how many numbers are greater then 20

Example #6

Now we will see how to count values that are not equal to a certain value. Take a look at the below COUNTIF example

how to count values

  • From this list, we need to count the values which are not equal to the word Government. This means we need to count all the values except the Government. Apply the below formula to get the total.

count values not equal to government

  • The total count of the words which are not equal to the word Government is 4.

total count of words

Things to Remember

  • All the text value criteria should be supplied in double-quotes.
  • Numerical values need not be supplied in double-quotes.
  • All the operator symbols also need to supply with double-quotes.
  • If the text value is referring to the cell, then no need for double-quotes.

Recommended Articles

This has been a guide to COUNTIF Examples in Excel. Here we discuss examples of COUNTIF Function in excel using a Drop-down list, Operator Symbol with a downloadable excel template. You may learn more about excel from the following articles –

  • What is the COUNTIFS Function?
  • VBA COUNTIF
  • Use COUNTIF with Multiple Criteria
  • AutoCorrect in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download COUNTIF Examples Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More