SUMIFS

Database Functions

(4.9/5)

Adds the values in a range that meet multiple criteria. More powerful than SUMIF for complex conditional summing.

Interactive Formula Tester

Parameters:

Formula:

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

Complete Theory & Understanding

Master the fundamentals of Excel SUMIFS function

Core Concept

SUMIFS is the enhanced version of SUMIF, supporting multiple AND conditions. It adds values from cells that meet ALL specified criteria simultaneously. Perfect for complex filtering scenarios where you need to sum data based on several conditions across different ranges.

Why Use SUMIFS?

Sum by multiple dimensions
Sum by segments
Sum in date ranges
Complex conditional sums

Key Characteristics

Multiple Criteria

Supports up to 127 criteria pairs

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

AND Logic

All criteria must be satisfied

Meets ALL conditions

Flexible Operators

Same operators as SUMIF

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

Required sum_range

Must specify range to sum

First parameter is always range

Function Anatomy

=SUMIFS(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Multi-Dimensional Analysis

Sum by multiple dimensions

Segmented Reporting

Sum by segments

Time-Based Analysis

Sum in date ranges

Advanced Filtering

Complex conditional sums

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Required

sum_range:

Range of cells to sum

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:

Sum of cells meeting all criteria

Description: Adds values for cells meeting multiple criteria

Interactive Examples

Multiple Criteria Sum

Sum with 2 conditions

Input Text:

"Region="North", Year=2023"

Formula:

=SUMIFS(Sales, Region, "North", Year, 2023)

Result:

Sum North 2023 sales

Returns sum meeting both criteria

VBA Implementation & Automation

Basic SUMIFS in VBA

Simple VBA implementation

' Basic SUMIFS in VBA
Sub SUMIFSExample()
    Dim result As Variant
    result = Application.WorksheetFunction.SumIfs(Range("C1:C100"), _
        Range("A1:A100"), "North", Range("B1:B100"), 2023)
    MsgBox "Sum: " & result
End Sub

' Dynamic criteria
Sub SumByMultipleCriteria()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim sumRange As Range
    Dim crit1Range As Range
    Dim crit2Range As Range
    
    Set sumRange = ws.Range("Sales")
    Set crit1Range = ws.Range("Region")
    Set crit2Range = ws.Range("Year")
    
    ws.Range("Result").Value = Application.SumIfs(sumRange, _
        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.SumIfs(ws.Range("Sales"), _
            ws.Range("Region"), regions(i), ws.Range("Year"), 2023)
    Next i
End Sub