EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel COUNTIF Examples in Excel
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

COUNTIF Examples in Excel

By Ashwani JaiswalAshwani Jaiswal

Countif Function Example in Excel

Excel COUNTIF Example (Table of Contents)

  • Excel COUNTIF Examples
  • Syntax of COUNTIF Example in Excel
  • How to implement Excel COUNTIF Examples?

Excel COUNTIF Examples

Excel COUNTIF Example counts the cells that meet certain criteria or conditions. It can be used to count cells that match specific criteria with text, numbers or dates. By referring to some COUNTIF examples in Excel, you can understand the use and implantation of the COUNTIF Function.

Syntax of COUNTIF Example in Excel

The Syntax of the COUNTIF Function includes 2 parameters. Before we apply COUNTIF, first let’s see the syntax of COUNTIF Function in Excel as shown below;

Countif Function Example 1-2

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Range = The range we need to select from where we will be getting the count.

Criteria = Criteria should be any exact word or number which we need to count.

The return value of COUNTIF in Excel is a positive number. The value can be zero or non-zero.

How to implement Excel COUNTIF Examples?

Using the COUNTIF Function in Excel is very easy. Let’s understand the working of the COUNTIF Function in Excel by some examples given below.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,768 ratings)
You can download this Excel COUNTIF Example Template here – Excel COUNTIF Example Template

Excel COUNTIF Example – Illustration #1

COUNTIF function in excel is used for counting any kind of cell content in selected range data. The selected cells may contain numbers or text. Here we have a list of some products which are repeating multiple times. Now we need to check how many times a product gets repeated.

Countif Function Example 1-1

As we can see in the above screenshot. We have some product types, and besides that, we have chosen a cell for counting cell of a specific product type.

For applying the COUNTIF Function example, go to the cell where we need to see output and type “=” (Equal) sign to enable all the inbuilt functions of excel. Now type COUNTIF and select it.

Range = Select the range as A2:A18.

Criteria = For text, let’s select the criteria as Mobile in inverted commas (” “); it is a text.

As we can see below the screenshot, how our applied COUNTIF final formula will look like. Blue colored cells are our range value, and in inverted commas, Mobile is our criteria to be calculated.

Countif Function Example 1-3

Once we press the Enter key, we will get the applied formula as shown below.

Countif Function Example 1-4

As we can see, the count of product type Mobile is coming as 5, which are also highlighted in yellow color in the above screenshot.

We can test different criteria to check the correctness of the applied formula.

Excel COUNTIF Example – Illustration #2

There is one more method of applying the COUNTIF Function in Excel. For this, put the cursor to the cell where we need to apply COUNTIF and then go to the Formula menu tab and click on Insert Function as shown in the below screenshot.

Countif Function Example 1-5

Once we click on it, we will get the Insert Function box which has all the list of inbuilt functions of excel as shown below. From the tab Or select a category, choose All to get the list of all functions.

Or select a category

And from the Select a function box, select COUNTIF and click on OK. Or else type COUNTIF or keyword related to this find related functions in Search for a function box.

Insert Function

After that, we will see the function argument box, where we need to select the same range as we did in Illustration #1 but with different criteria as Desktop and click on OK.

Criteria

If the formula is correct, we will see the formula resulting in the Function arguments box itself, as highlighted. After that, we will get the result in the output cell, as shown below.

Output Result

As we can see in the above screenshot, the count of Desktop is coming as 4. Which are also highlighted in yellow color in the above screenshot?

For this process also we can test different criteria to check the correctness of the applied formula.

This is how the COUNTIF function is used to calculate the numbers or words that are repeating multiple times. This is quite helpful where the data is so huge that we could not be able to apply filters.

Excel COUNTIF Example – Illustration #3

Let’s see one more example of COUNTIF Function in Excel. We have a list of some students where student marks of Subject X and Subject Y are mentioned in columns B and C. Now with the help of the COUNTIF Function Example, we will see how many students got 19 Marks out of 20.

Student's Data

For this, go to the cell where we need to see the output. Type = (Equal) sign and search for COUNTIF function and select it as shown below.

Countif Function

Now select the range. Here, as we have two columns where we can count the values, we will select columns B and C from cell B2 to B6. By this, we will be covering the B2 to C6 cells range. Now for the criteria, type 19 in inverted commas, as shown below.

Formula in Cell

After that, press the Enter key to apply the formula, as shown below.

Applying the Formula

As we can see in the above screenshot, the COUNTIF function counted that only 2 students got marks that are 19 in any subjects.

Here, by applying COUNTIF functions where the range is more than one column, the function itself checks the criteria in the whole selected range and gives the result. Now, as per the above marks, Kane and Reema are those students who got 19 marks in one of the subjects. There could be cases where we could get 19 marks against a single entry irrespective of the range selected, but the output will be the combined result of data available in the complete selected range.

Things to Remember

  • The second parameter in the formula “Criteria” is case-insensitive.
  • As a result, only the values that meet the criteria will be returned.
  • If the wildcard characters are to be used as they are in the criteria, the tilde operator must precede them, i.e. ‘~? ‘, ‘ *‘.

Recommended Articles

This has been a guide to Examples of COUNTIF Function in Excel. Here we discuss how to use COUNTIF Example in excel along with practical illustrations and a downloadable excel template. You can also go through our other suggested articles –

  1. COUNTIF with Multiple Criteria
  2. COUNTIFS in Excel
  3. COUNTIF Examples in Excel
  4. COUNTIF Formula in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for Marketing Training (8 Courses, 13+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Excel COUNTIF Example Template

EDUCBA

Download Excel COUNTIF Example Template

EDUCBA

डाउनलोड Excel COUNTIF Example Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more