COUNTIFS

Database Functions

(4.9/5)

Counts the number of cells in multiple ranges that meet multiple criteria. More powerful than COUNTIF for complex conditional counting.

Interactive Formula Tester

Parameters:

Formula:

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

Complete Theory & Understanding

Master the fundamentals of Excel COUNTIFS function

Core Concept

COUNTIFS is the enhanced version of COUNTIF, supporting multiple AND conditions. It counts cells across multiple ranges where ALL specified criteria are met simultaneously. Perfect for complex filtering scenarios where you need to count data based on several conditions across different ranges.

Why Use COUNTIFS?

Count by multiple dimensions
Count by segments
Count in date ranges
Complex conditional counts

Key Characteristics

Multiple Criteria

Supports up to 127 criteria pairs

COUNTIFS(..., range1, crit1, range2, crit2)

AND Logic

All criteria must be satisfied

Meets ALL conditions

Flexible Operators

Same operators as COUNTIF

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

Range Alignment

All ranges must align

Same dimensions required

Function Anatomy

=COUNTIFS(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Multi-Dimensional Analysis

Count by multiple dimensions

Segmented Reporting

Count by segments

Time-Based Analysis

Count in date ranges

Advanced Filtering

Complex conditional counts

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

Required

criteria_range1:

First range to evaluate with criteria

Required

criteria1:

First criterion to apply

Optional

criteria_range2, criteria2, ...:

Additional ranges and criteria (up to 127 pairs)

Returns

Return Value:

Count of cells meeting all criteria

Description: Counts cells meeting multiple criteria across ranges

Interactive Examples

Multiple Criteria Count

Count with 2 conditions

Input Text:

"Region="North", Year=2023"

Formula:

=COUNTIFS(Region, "North", Year, 2023)

Result:

Count

Counts rows meeting both criteria

VBA Implementation & Automation

Basic COUNTIFS in VBA

Simple VBA implementation

' Basic COUNTIFS in VBA
Sub COUNTIFSExample()
    Dim result As Long
    result = Application.WorksheetFunction.CountIfs(Range("A1:A100"), "North", _
        Range("B1:B100"), 2023)
    MsgBox "Count: " & result
End Sub

' Dynamic criteria
Sub CountByMultipleCriteria()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim crit1Range As Range
    Dim crit2Range As Range
    
    Set crit1Range = ws.Range("Region")
    Set crit2Range = ws.Range("Year")
    
    ws.Range("Result").Value = Application.CountIfs(crit1Range, "North", _
        crit2Range, 2023)
End Sub

' Complex report
Sub BuildComplexReport()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim regions() As String
    Dim i As Integer
    
    regions = Array("North", "South", "East", "West")
    
    For i = 0 To UBound(regions)
        ws.Cells(i + 1, 1).Value = regions(i)
        ws.Cells(i + 1, 2).Value = Application.CountIfs(ws.Range("Region"), regions(i), _
            ws.Range("Year"), 2023)
    Next i
End Sub