SUMIF

Adds the cells specified by a given criteria

Download reference workbook

Syntax

SUMIF(range, criteria, [sum_range])

Square brackets indicate optional arguments.

Arguments

rangeRequired: The range of cells that you want evaluated by criteria.A1:A20
criteriaRequired: The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.
Full description
The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included - a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
">5"
sum_rangeOptional: The actual cells to add, if you want to add cells other than those specified in the range argument.
Full description
The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
B1:B20

G2Editable

Loading your interactive spreadsheet…

Preparing your worksheet…

Example notes

To sum everything on or before October 15 when dates include times, use "<"&DATE(2026,10,16): an exclusive next-day boundary includes midnight, noon and 23:59:59 on October 15 and excludes October 16 midnight. The result is 12 + 25 - 5 = 32. By contrast, "<="&DATE(2026,10,15) includes only the midnight row here and returns 12. A time is a fraction of a day added to a numeric date serial. Column A contains text labels; the formula compares numeric values in B. This is a single upper bound, not a date window; add a lower bound with SUMIFS if required.

=SUMIF(B2:B5,"<"&DATE(2026,10,16),C2:C5)

Inspect formula

Inspect the current formula

Read its written arguments or observe actual operation values in a separate local calculation. This action neither applies a draft nor grades an answer.

Explore

Read the formula, one argument at a time

Explore the written function calls and their inputs. The calculation result belongs to the complete formula.

Function call

SUMIF · top-level call

=SUMIF(B2:B5,"<"&DATE(2026,10,16),C2:C5)

1 / 3 · range

The range of cells that you want evaluated by criteria.

SUMIF reference ↗

Follow the cells behind a result

Read explicit local references and helper formulas using a separate calculation. Your draft stays in this field; the worksheet and practice checks do not change.

Downloads & compatibility

The worksheet runs locally. Published checks apply to the supplied example only. Native Excel and Google Sheets compatibility remain unverified.

SUMIF dates less than or equal to a cutoff

Use one condition to total matching amounts. Compare a region filter, an inclusive date-only cutoff and an exclusive next-day boundary for timestamps. Each example includes the complete source, formula and expected result.

Total amounts from one region

Total amounts from one region. The result is checked against a separately authored expected value for the supplied table. You can edit the data to explore the rule.

=SUMIF(C2:C6,"East",A2:A6)

Source data: Playground
Amount	Label	Region	Date serial	Status
12	Harbor	East	46296	Paid
25	Cedar	West	46301	Pending
-5	Harbor	East	46310	Paid
18	Lumen	East	46326	Paid
0		West	46327	Paid

Expected G2: 25

Open this example in the worksheet

Sum dates less than or equal to a cutoff

For dates without times, "<="&DATE(2026,10,15) includes October 15. Join the quoted comparison operator to DATE with &: the condition tests numeric dates in B2:B5 and sums the matching amounts in C2:C5. The first three rows contribute 12 + 25 - 5 = 32. Column A contains readable text labels only; column B contains the actual date serials used for calculation. Convert imported text dates before comparing them. Dates use the 1900 date system; existing 1904-system workbooks need their own matching dates.

=SUMIF(B2:B5,"<="&DATE(2026,10,15),C2:C5)

Source data: Example2
Date label (text)	Date serial	Amount
2026-10-14	46309	12
2026-10-15	46310	25
2026-10-15	46310	-5
2026-10-16	46311	18

Expected G2: 32

Open this example in the worksheet

Include the whole cutoff day when dates contain times

=SUMIF(B2:B5,"<"&DATE(2026,10,16),C2:C5)

Source data: Example3
Timestamp label (text)	Date/time serial	Amount
2026-10-15 00:00:00	46310	12
2026-10-15 12:00:00	46310.5	25
2026-10-15 23:59:59	46310.99998842592	-5
2026-10-16 00:00:00	46311	18

Expected G2: 32

Open this example in the worksheet

The supplied examples are checked in the browser engine. Native Excel and Google Sheets recalculation remain unverified.

Reference and compatibility

Sum the values in a range that meet criteria that you specify.

Microsoft function documentation ↗

Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.

Univer 1.0.4 / Apache-2.0