SUMIF

Database Functions

(4.9/5)

Adds the values in a range that meet a single criterion. Essential for conditional summing and data aggregation.

Interactive Formula Tester

Criteria:

Formula:

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

Complete Theory & Understanding

Master the fundamentals of Excel SUMIF function

Core Concept

SUMIF adds values from cells that meet a single specified condition. It filters data by the criterion before summing, making it essential for conditional aggregation. When sum_range is omitted, the function sums the values in the criteria range itself.

Why Use SUMIF?

Sum sales by condition
Sum by category
Sum revenue by criteria
Sum costs by condition

Key Characteristics

Single Criterion

Filters by one condition

SUMIF(..., ">1000")

Multiple Criteria Types

Supports various operators and wildcards

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

Flexible Ranges

Optional separate sum range

SUMIF(region, "North", sales)

Text and Numeric

Works with both data types

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

Function Anatomy

=SUMIF(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Sales Aggregation

Sum sales by condition

Financial Totals

Sum by category

Revenue Tracking

Sum revenue by criteria

Cost Analysis

Sum costs by condition

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=SUMIF(range, criteria, sum_range)

Required

range:

The range of cells to evaluate with criteria

Required

criteria:

Condition or criteria to apply to the range

Optional

sum_range:

Range to sum (if different from range)

Returns

Return Value:

Sum of cells meeting the criteria

Description: Adds values for cells in range that meet criteria

Interactive Examples

Basic Conditional Sum

Sum values above threshold

Input Text:

"Sales > 1000"

Formula:

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

Result:

Sum of values > 1000

Returns sum of values exceeding 1000

VBA Implementation & Automation

Basic SUMIF in VBA

Simple VBA implementation

' Basic SUMIF in VBA
Sub SUMIFExample()
    Dim result As Variant
    result = Application.WorksheetFunction.SumIf(Range("A1:A100"), ">1000")
    MsgBox "Sum: " & result
End Sub

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

' Multiple calculations
Sub BuildSumReport()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim criteriaList() As Variant
    Dim i As Integer
    
    criteriaList = Array(">0", ">100", ">500", ">1000")
    
    For i = 0 To UBound(criteriaList)
        ws.Cells(i + 1, 2).Value = criteriaList(i)
        ws.Cells(i + 1, 3).Value = _
            Application.SumIf(ws.Range("A1:A100"), criteriaList(i))
    Next i
End Sub