• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

COUNTIF Excel Function

Home » Excel » Blog » Statistical Functions in Excel » COUNTIF Excel Function

COUNTIF Function in Excel

COUNTIF Function in excel (Table of Contents)

  • COUNTIF Function in Excel
  • COUNTIF Formula in Excel
  • How to Use COUNTIF Function in Excel?

COUNTIF Function in Excel

Suppose we have a sales team and we want to see the count of sales by each team member. The COUNTIF Function helps us to do this.

Start Your Free Excel Course

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

What is COUNTIF Function?

COUNIF Function counts the cells that meet specific criteria or condition. It can be used to count cells with text, numbers or dates that match specific criteria.

The COUNTIF function also works with logical operators (<, >, <>, =) and wildcards (*, ?).

COUNTIF Formula in Excel

Below is the COUNTIF Formula in Excel :

COUNTIF Formula in Excel

The COUNTIF Function in Excel has two arguments i.e. range, criteria.

  • Range: The range of cells to count. We give the range in a formula e.g. A1:A10.
  • Criteria: This defines the condition that tells the function which cells to count. It can be a number, text string, cell reference, or expression.

COUNTIF Function comes under statistical function category and it’s a built-in function in excel. You can find this function under the ‘Formulas’ tab. Please follow the below steps:

  • Click on Formulas tab.

COUNTIF Function step 1

  • Select More Functions ribbon.

COUNTIF Function step 2

  • Click on Statistical option.

COUNTIF Function step 3

  • Then it will open the drop-down list in the left side as per below screenshot:

COUNTIF Function step 4

  • Please select COUNTIF function from the drop-down list.

COUNTIF Function step 5

  • It will open the Function Arguments pop-up as per the below screenshot:

COUNTIF Function step 6

  • Under the Range field, enter a range of cells from which you want to count.

COUNTIF Function step 7

  • Under the criteria field, enter the condition in the form of a text, expression or a number that specifies which cells to be counted.

COUNTIF Function step 8

How to Use COUNTIF Function in Excel?

COUNTIF Function in Excel is very simple and easy to use. Let understand the working of COUNTIF Function in Excel by some COUNTIF Formula example.

Popular Course in this category
Cyber Week Sale
Excel Advanced Training (14 Courses, 21+ Projects) 14 Online Courses | 21 Hands-on Projects | 129+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (2,613 ratings)
Course Price

View Course

Related Courses
Excel for Marketing Training (5 Courses, 12+ Projects)
You can download this COUNTIF Function Excel Template here – COUNTIF Function Excel Template

COUNTIF Excel Function – Example #1

We have the sales data of a company XYZ and we need to find out which salesperson sale the maximum and from which city, we are ahead in profit. So that we can expand our business and can distribute the bonus accordingly as per the sales employee performance.

COUNTIF Example 1-1

Now if we summarize the above data, we can predict the below results:

COUNTIF Example 1-2

Here we will use the COUNTIF Function.

  • For Count of sales, we will take a range of sales amount.

COUNTIF Example 1-3

The result will be :

COUNTIF Example 1-4

  • For sales are done by sales employee will take the range of sales employee field.

COUNTIF Example 1-5

The result will be :

COUNTIF Example 1-6

  • For sales city wise will take the City field as the first parameter.

COUNTIF Example 1-7

The result will be :

COUNTIF Example 1-8

Apply the COUNTIF formula as per the required criteria and the result is:

COUNTIF Example 1-9

COUNTIF Excel Function – Example #2

Highlight unique values in the below data

COUNTIF Example 2-1

For calculating the duplicate/Unique values, we will use the below formula: 

=COUNTIF(data, A1)=1

Explanation:

  • Although Excel has many built-in options for highlighting data with conditional formatting. We also can highlight the data by using a formula.
  • For highlighting cells that contain unique values in a set of data, we can use a formula that returns TRUE when a value occurs just once.

If you have values in the cells A16:D22 want to highlight cells with duplicate values, we will use below formula:

=COUNTIF($A$16:$D$22,A16)=1

COUNTIF Example 2-2

How this formula works:

COUNTIF Function counts each value the number of times in the data set or range. According to this formula, each value must occur at least once in the data set, so when the count equals to 1, the value is unique.

 COUNTIF Example 2-3

Things to remember about COUNTIF Function in Excel

  • In criteria, the wildcard characters? and * can also be used. A question mark matches any one character and an asterisk matches any sequence of characters.
  • Before using COUNTIF Function in Excel, we need to be understood properly that Where do we want to look? and What do we want to look for?
  • The second parameter ‘Criteria’ in the formula is case insensitive.

Recommended Articles

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

  1. How to Use SUBTOTAL Function in Excel?
  2. How to Use LN Function in Excel?
  3. How to Use TANH Function in Excel?
  4. How to Use TRUE Function in Excel?

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

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

Primary Sidebar
Excel Functions Tutorials
  • Statistical Functions 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
    • VBA Function in Excel
    • 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 (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • VBA (180+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
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
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download COUNTIF Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Cyber Week Offer - Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More