Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel VLOOKUP Function
  • How to get last row in numeric data in Excel

Data Analysis Examples

  • How to create a Histogram in Excel
  • Excel Bar Chart
  • How to Create Thermometer Chart in Excel
  • How to calculate average last N values in a table in Excel
  • How to create running total in an Excel Table

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation must not contain
  • How To Create Drop-down List in Excel
  • Excel Data validation unique values only
  • Excel Data validation must begin with

Countif function in Excel

by

COUNTIF is one of the statistical functions, it is used to count the number of cells that meet a criterion

This chapter covers  many easy to follow COUNTIF examples like Numbers, Text, Booleans, Errors and ‘Or Criteria’.

Numbers

1. The COUNTIF function below counts the number of cells that contain the value 20.

2. The following COUNTIF function gives the exact same result. It counts the number of cells that are equal to the value in cell C1.

3. The COUNTIF function below counts the number of cells that are greater than or equal to 10.

4. The following COUNTIF function gives the exact same result. It uses the & operator to join the ‘greater than or equal to’ symbol and the value in cell C1.

5. The COUNTIF function below counts the number of cells that are not equal to 7.

6. The COUNTIF functions below count the number of cells that are equal to 3 or 7.

Worked Example:   Count cells that contain specific text in Excel

7. The COUNTIF function below counts the number of cells that are less than the average of the values (8.2).

Text

1. The COUNTIF function below counts the number of cells that contain exactly star.

2. The COUNTIF function below counts the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.

3. The COUNTIF function below counts the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

4. The COUNTIF function below counts the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way.

5. The COUNTIF function below counts the number of cells that contain text.

Note: visit our page about counting cells with text for more information and examples.

Booleans

1. The COUNTIF function below counts the number of cells that contain the Boolean TRUE.

Worked Example:   Count Unique Values in Excel

2. The COUNTIF function below counts the number of cells that contain the Boolean FALSE.

3. The COUNTIF functions below count the number of cells that contain the Boolean TRUE or FALSE.

Errors

1. You can also use the COUNTIF function to count specific errors in Excel. For example, count the number of cells that contain the #NAME? error.

2. The array formula below counts the total number of errors in a range of cells.

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Counting Errors for detailed instructions on how to create this array formula.

Or Criteria

Counting with Or criteria in Excel can be tricky.

1. The COUNTIF functions below count the number of cells that contain Google or Facebook (one column).

Worked Example:   Get nth match with VLOOKUP in Excel

2. However, if you want to count the number of rows that contain Google or Stanford (two columns), you cannot simply use the COUNTIF function twice (see the picture below).

Note: rows that contain Google and Stanford are counted twice, but they should only be counted once. 4 is the answer we are looking for.

3. The array formula below does the trick.

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Counting with Or Criteria for detailed instructions on how to create this array formula and become an Excel pro.

4. Counting with And criteria is a lot easier. For example, to count the number of rows that contain Google andStanford, simply use the COUNTIFS function (with the letter S at the end).

Note: the COUNTIFS function in Excel counts cells based on two or more criteria.

Post navigation

Previous Post:

RAND vs RANDBETWEEN function examples in Excel

Next Post:

Understanding Named Range, Named Constant, Name Manager in Excel Formulas

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to count specific characters in a cell in Excel
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to get top level domain (TLD) in Excel
  • How to check if cell contains some words but not others in Excel
  • How to count specific characters in a range in Excel
  • Convert date to month and year in Excel
  • Get last working day in month in Excel
  • Get month from date in Excel
  • Generate series of dates by weekends in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles