EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel COUNTIF Formula 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 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 Formula in Excel

By Pradeep SPradeep S

COUNTIF Formula in Excel

Excel COUNTIF Formula (Table of Contents)

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

COUNTIF Formula in Excel

COUNTIF Formula in excel is an inbuilt or pre-built integrated function which is categorized under the statistical group of formulae.

Start Your Free Excel Course

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

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,325 ratings)

Excel COUNTIF Formula counts the number of cells within a specified array or range based on a specific criterion or applied condition.

Below is the Syntax of the COUNTIF Formula in Excel :

COUNTIF Formula

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

  1. Range: (Required & Compulsory argument): The range or array of cells on which the criteria should be applied, here array or range should be mentioned as, e.g. C1:C12.
  2. Criteria: (Required & Compulsory argument)) It is a specific condition which will be applied to the cell values presented by the range of cells. It indicates the Count IF formula “what cells need to be counted” here. There is a limitation of the criteria argument in the COUNTIF Formula, i.e. If the text string in the criteria argument contains greater than 255 characters in length or more than 255 characters. Then it returns #VALUE! error.

Note: It can be either a cell reference or text string or logical or mathematical expression.

  • A named range can also be used in the COUNTIF formula.
  • In the Criteria argument, Non-numeric values compulsorily should always be enclosed within double-quotes.
  • Criteria argument is case-insensitive, where it can consider any text case, it may be proper or upper or lower case.
  • In the Excel COUNTIF formula, the below-mentioned arithmetic operators can also be used in the criteria argument

=             Equal to
<             Less Than
<=          Less than or equal to
>             Greater Than
>=          Greater than or equal to
<>          Less than or greater than

How to Use COUNTIF Formula in Excel?

COUNTIF Formula in Excel is very simple and easy to use. Let’s understand the working of the COUNTIF Formula in Excel with some examples.

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

Excel COUNTIF Formula – Example #1

In the following example, the Table contains company employees name in column A (A2 to A15) & funds transferred status in column B (B2 to B15). Here I need to find out the count of two parameters, i.e. funds transferred & funds not transferred in the dataset range (B2 to B15).

Example 1

  • Let’s apply the COUNTIF function in cell D4. Select cell D4 where the COUNTIF formula needs to be applied.

COUNTIF Formula Example 1-2

  • Click or select an Insert Function (fx) button in the formula toolbar; a dialog box will appear, where you need to enter or type the keyword countif in the Search for a function box, COUNTIF formula will appear in select a function box. Double click on the COUNTIF formula.

COUNTIF Formula Example 1-3

  • A function argument dialog box appears where syntax or arguments for the COUNTIF formula needs to be entered or filled. i.e. =COUNTIF (Range, Criteria)

COUNTIF Formula Example 1-4

  • Range: It is a range of cells where you want to count. Here the range or array is from B2 to B15, So select the column range. i.e. B2:B15

COUNTIF Formula Example 1-5

  • Criteria: It is a condition or criteria where you insert the COUNTIF function, which cells need to be counted, i.e. “Y” (here, we need to find out the count of an employee who has received a number of funds). Click OK after entering both range & criteria argument. =COUNTIF (B2:B15,”Y”)

COUNTIF Formula Example 1-6

  • COUNTIF formula returns the count of an employee who has received fund, i.e. 8 from the dataset range (B2:B15). Here, the COUNTIF formula returns a count. 8 within a defined range. It means eight number of employees have received funds.

Result of Example 1-1

  • Suppose I want to find out the number or count of employees who have not received funds. Just I need to change the criteria argument, i.e. instead of “Y”, I need to use “N” =COUNTIF (B2:B15,”N”)

COUNTIF Formula Example 1-8

  • COUNTIF formula returns the count of an employee who has not received a fund, i.e. 6 from the dataset range (B2:B15). Here, the COUNTIF formula returns a count of 6 within a defined range. It means six number of employees who haven’t received funds.

Result of Example 1-2

Excel COUNTIF Formula – Example #2

With Arithmetic Operator in criteria argument

In the second example, the Table contains students name in column A (A2 to A15), Total marks scored in column B (B2 to B15) and their percentage in column C (C2 to C15); here, I need to find out the count or number of students who have scored more than or equal to 90% in a dataset range (C2 to C15).

Example 2

  • Now, we can apply the COUNTIF formula with an arithmetic operator in the cell E3. i.e. =COUNTIF (Range, criteria)

COUNTIF Formula Example 2-2

  • Range: It is a range of cells where you want to count. Here the range or array is from C2 to C15, So select the column range. i.e. C2:C15.

COUNTIF Formula Example 2-3

  • Criteria: It is a condition or criteria where you inform the count of function, which cells need to be counted, i.e. “>=90%” (Here I need to find out the count or number of Students who has scored more than or equal to 90% in a dataset range (C2 to C15)).

COUNTIF Formula Example 2-4

  • Here, the =COUNTIF (C2:C15,”>=90%”) formula returns a value 3, i.e. No of Students who have scored more than or equal to 90% in a dataset range (C2 to C15).

Result of Example 2

Things to Remember About COUNTIF Formula in Excel

The Wildcard characters can also be used in the excel COUNTIF formula (in criteria argument) to get the desired result; Wildcards helps out in partial matching.

The three most commonly & widely used wildcard characters in the Excel COUNTIF formula are

  • Asterisk (*): To match any sequence of either trailing or leading characters. E.g. Suppose if you want to match all cells in a range containing a text string beginning with the letter “T” and ending with the letter “e”, You can enter the condition or criteria argument as “T*e”.
  • tilde (~): To find an actual question mark or asterisk. E.g. Suppose if you want to match all the cells in a range containing Asterisk (*) or Question mark (?) in the text, then tilde (~) is used in condition or criteria argument as “*~*shirt*” E.g. Red*shirt Green shirt.

In the above example, it will track the Red*shirt because it contains Asterisk (*) character in between

Asterisk (*) usage examples

TOR* Finds the text containing or Starting with TOR

*TOR Finds the text containing or Ending with TOR

*TOR* Finds the text Containing the word TOR

  • Question mark (?): Used to identify or track any single character. e.g. Suppose if you want to track “shirts” word or words ending with “S” in the below-mentioned example, then in the criteria argument, I need to use “shirt?”, it will result in the value 1.

Redshirts

Green shirt

Recommended Articles

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

  1. COUNTIF with Multiple Criteria
  2. COUNTIF Examples in Excel
  3. Count Characters in Excel
  4. COUNTIF Excel Function
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 COUNTIF Formula Excel Template

EDUCBA

Download COUNTIF Formula Excel Template

EDUCBA

डाउनलोड COUNTIF Formula Excel 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