Excel WhisperExcel Whisper

How to Use COUNTIF Function in Excel

Complete guide to Excel's powerful conditional counting function with practical examples

COUNTIF is an essential Excel function that counts the number of cells within a range that meet a specific condition. Whether you need to count sales transactions in a particular region, tally items with certain attributes, or analyze data patterns, COUNTIF provides a quick and efficient solution. Learn through practical examples from basic counting operations to more advanced filtering techniques.

Try COUNTIF Online

Test COUNTIF function directly in your browser - faster and easier than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

COUNTIF Function Guide

Step-by-step instructions for using COUNTIF

Function Syntax

=COUNTIF(range, criteria)

Steps to Use

1

Select the cell where you want the count result

2

Start with =COUNTIF(

3

Select the range of cells to evaluate (e.g., A2:A100)

4

Enter your criteria (e.g., "East", ">50", or "*Smith*")

5

Close with a parenthesis and press Enter

Use Cases

Data Analysis

Count occurrences of specific values or conditions in datasets

Inventory Management

Track the number of products meeting certain criteria

Performance Evaluation

Count instances where performance metrics meet or exceed targets

Tips & Notes

  • 1Text criteria must be enclosed in double quotes (e.g., "East")
  • 2Numeric criteria can use comparison operators (e.g., ">50", "<=100")
  • 3Date criteria should use the proper format (e.g., ">="&DATE(2023,1,1))
  • 4You can use wildcards (* for any sequence of characters, ? for any single character) in text criteria
  • 5COUNTIF is not case-sensitive for text comparisons
  • 6To count cells that are not blank, use "<>" as the criteria
  • 7To count cells that contain errors, use COUNTIF in combination with ISERROR
  • 8For multiple criteria, use the COUNTIFS function instead

Frequently Asked Questions about COUNTIF

Common questions and solutions for Excel COUNTIF function

COUNTIF allows you to count cells based on a single criterion, while COUNTIFS lets you apply multiple criteria across different ranges. COUNTIFS is more powerful for complex counting where you need to meet several conditions simultaneously, such as counting sales transactions for a specific region, product category, and time period.

To count cells containing specific text, use wildcards with COUNTIF: =COUNTIF(range, "*search_text*"). The asterisks (*) match any characters before and after your search text. For example, =COUNTIF(A1:A20, "*apple*") counts cells containing the word 'apple' anywhere in the text.

Yes, to count blank cells, use: =COUNTIF(range, ""). To count non-blank cells, use: =COUNTIF(range, "<>"). These formulas work for truly empty cells, but cells with formulas that return empty strings (="") are not considered blank by COUNTIF.

For date criteria, use comparison operators with the DATE function: =COUNTIF(date_range, ">="&DATE(2023,1,1)). This counts cells where the dates are on or after January 1, 2023. Make sure your date criteria are formatted as dates or use the DATE function to create date values.

This typically happens when numbers are stored as text in your cells. COUNTIF treats numbers and text differently, even if they look the same. To fix this, either convert the text to numbers using the VALUE function or include both formats in your criteria: =COUNTIF(range, 42) + COUNTIF(range, "42").

To count cells within a range of values, use COUNTIFS with two criteria on the same range: =COUNTIFS(range, ">="&lower_bound, range, "<="&upper_bound). For example, =COUNTIFS(A1:A20, ">=50", A1:A20, "<=100") counts values between 50 and 100 inclusive.

COUNTIF is not case-sensitive by default. For case-sensitive counting, you can create a helper column using the EXACT function to create TRUE/FALSE flags, then use COUNTIF to count only where the flags are TRUE. Alternatively, in newer Excel versions, you can use a combination of FILTER and COUNTA functions for case-sensitive operations.

COUNTIF cannot directly count error values. Instead, use COUNTIF with ISERROR: =SUMPRODUCT(--ISERROR(range)). This formula counts all cells containing any error value (#N/A, #VALUE!, #DIV/0!, etc.) in the specified range.