COUNTIF

Database Functions

(4.9/5)

Counts the number of cells in a range that meet a single criterion. Essential for conditional counting and data analysis.

Interactive Formula Tester

Criteria:

Formula:

=COUNTIF(">50, 10,20,30,40,50,60,70,80,90,100")

Complete Theory & Understanding

Master the fundamentals of Excel COUNTIF function

Core Concept

COUNTIF counts cells that meet a single specified condition. It filters data by the criterion and returns the number of matching cells. Essential for frequency analysis, data validation, and conditional counting across spreadsheets. Unlike COUNT, it evaluates conditions before counting.

Why Use COUNTIF?

Count occurrences
Verify data entries
Count by performance
Count by category

Key Characteristics

Single Criterion

Filters by one condition

COUNTIF(..., ">1000")

Multiple Operators

Supports various comparison operators

>, <, =, <>, *, ?

Text and Numeric

Works with both data types

COUNTIF(..., "Widget") or COUNTIF(..., ">1000")

Non-Volatile

Doesn't recalculate on change

Stable calculations

Function Anatomy

=COUNTIF(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Frequency Analysis

Count occurrences

Data Validation

Verify data entries

Performance Tracking

Count by performance

Inventory Counting

Count by category

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=COUNTIF(range, criteria)

Required

range:

The range of cells to count

Required

criteria:

Condition or criteria to apply to the range

Returns

Return Value:

Count of cells meeting the criteria

Description: Counts cells in range that meet criteria

Interactive Examples

Basic Conditional Count

Count values above threshold

Input Text:

"Sales > 1000"

Formula:

=COUNTIF(A1:A100, ">1000")

Result:

Number of values > 1000

Returns count of values exceeding 1000

VBA Implementation & Automation

Basic COUNTIF in VBA

Simple VBA implementation

' Basic COUNTIF in VBA
Sub COUNTIFExample()
    Dim result As Long
    result = Application.WorksheetFunction.CountIf(Range("A1:A100"), ">1000")
    MsgBox "Count: " & result
End Sub

' Dynamic criteria
Sub CountByCriteria()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim criteria As String
    Dim result As Long
    
    criteria = InputBox("Enter criteria (e.g., >1000)")
    result = Application.CountIf(ws.Range("A1:A20"), criteria)
    ws.Range("C1").Value = result
End Sub

' Frequency analysis
Sub FrequencyAnalysis()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim categories() As String
    Dim i As Integer
    
    categories = Array("Widget", "Gadget", "Device")
    
    For i = 0 To UBound(categories)
        ws.Cells(i + 1, 2).Value = categories(i)
        ws.Cells(i + 1, 3).Value = Application.CountIf(ws.Range("A1:A100"), categories(i))
    Next i
End Sub