Microsoft Excel SUMIF Examples and How to Use Them

In Microsoft Excel, SUMIF adds values that meet one condition. For example, with categories in A2:A6 and amounts in B2:B6, =SUMIF(A2:A6,"Rent",B2:B6) adds the amounts beside Rent and returns 2400 if the two Rent entries are 1,200 each. The examples below show how to adapt that pattern for numbers, text, dates, and more.

How SUMIF works

SUMIF has this structure: =SUMIF(range,criteria,[sum_range]). The range is the set of cells Excel checks, criteria is the condition, and sum_range is the set of values to add. The square brackets in the function description mean that sum_range is optional; they are not typed into a formula.

If you leave out sum_range, Excel adds the cells in range that meet the condition. If you provide sum_range, Excel checks the first range and adds the corresponding cell in the second. The ranges should cover corresponding rows and have the same dimensions. For example, a category in A2 is paired with the amount in B2, a category in A3 with B3, and so on.

Criteria can be text, a number, a comparison such as greater than a value, a wildcard pattern, or a reference to a cell containing the condition. SUMIF is not case-sensitive: criteria for “east” will match “East.” It evaluates one condition at a time, which matters when a task has several filters.

Basic example: add amounts for one category

Suppose A2:A6 contains Rent, Food, Rent, Food, and Utilities, while B2:B6 contains 1200, 85, 1200, 65, and 140. With categories in A2:A6 and amounts in B2:B6, =SUMIF(A2:A6,"Rent",B2:B6) returns 2400, adding the two Rent amounts. The formula checks column A but adds the matching values from column B.

To make the condition easy to change, put the category name in D2. With categories in A2:A6, amounts in B2:B6, and the requested category in D2, =SUMIF(A2:A6,D2,B2:B6) returns 2400 when D2 contains Rent. Change D2 to Food and the result becomes 150. This is useful for a small summary table: list categories in one column and copy the formula alongside them, adjusting the reference to the category cell for each row.

For a single-column list where the entries are also the values to add, the sum_range can be omitted. With amounts in B2:B10 and a threshold value of 100 in D2, =SUMIF(B2:B10,">100") adds only amounts greater than 100. This form is less common for category-and-amount data, but it is handy when filtering a list of numbers directly.

Number conditions and criteria references

Comparison operators let you sum values associated with numbers above, below, equal to, or different from a threshold. Excel recognizes operators including >, >=, <, <=, =, and <>. When an operator and a fixed number are typed directly as the criterion, put them together inside quotation marks.

For example, assume A2:A5 contains units sold (42, 58, 70, 55) and B2:B5 contains sales amounts (300, 450, 500, 250). With units in A2:A5 and amounts in B2:B5, =SUMIF(A2:A5,">50",B2:B5) returns 1200: it adds 450, 500, and 250 because their unit counts exceed 50. An amount of exactly 50 would not qualify; use the greater-than-or-equal operator if it should count.

A cell reference makes the threshold adjustable without editing the formula. With units in A2:A5, amounts in B2:B5, and the threshold in D2, =SUMIF(A2:A5,">"&D2,B2:B5) returns 1200 when D2 contains 50. The ampersand joins the comparison operator to the value in D2. If D2 changes to 60, the formula recalculates using the new threshold.

To add values that do not match a particular label, use the not-equal operator. With status labels in A2:A5 and amounts in B2:B5, =SUMIF(A2:A5,"<>Paid",B2:B5) adds amounts whose status is not Paid. Check how blank cells should be treated in your data before relying on this kind of condition: blanks are not the same as a meaningful status such as Unpaid.

Text criteria and wildcards

For an exact text match, put the text in quotation marks in the formula, or refer to a cell that contains the text. A criteria cell is often more convenient when users need to select a name or category. Keep spelling consistent in the source data; although SUMIF ignores letter case, an extra space or a different spelling can prevent an expected match.

Wildcards help when the text is only partly known. An asterisk (*) stands for any number of characters, including none; a question mark (?) stands for exactly one character. For example, “North*” can match North cap and North socks. If A2:A5 contains North socks, South socks, North cap, and North sock liners, and B2:B5 contains 20, 30, 15, and 12, then with names in A2:A5 and amounts in B2:B5, =SUMIF(A2:A5,"North*",B2:B5) returns 47.

Use a tilde (~) before a wildcard when you want to match the wildcard character itself rather than use it as a pattern. For instance, a tilde can make a question mark in a product description behave as literal text. This distinction is useful in catalogs or imported data where punctuation may appear in the actual entries.

Summing values within a date period

Excel stores real dates as serial numbers, so date criteria can use comparison operators. A reliable approach is to build the boundary with the DATE function rather than typing a date string whose interpretation could vary with regional settings. Ensure the cells contain actual Excel dates, not text that only looks like a date.

Suppose A2:A5 contains January 5, January 18, February 2, and January 31, 2026, and B2:B5 contains 150, 250, 300, and 100. To total January only, use SUMIFS because the dates must be on or after January 1 and before February 1. With dates in A2:A5 and amounts in B2:B5, =SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1)) returns 500. The upper boundary is exclusive, so a date at midnight on February 1 is not accidentally included. This approach also works for months with different numbers of days.

For a single boundary, SUMIF can be enough. With dates in A2:A5 and amounts in B2:B5, =SUMIF(A2:A5,">="&DATE(2026,1,1),B2:B5) adds amounts on or after January 1, 2026. It does not stop at the end of January; use a second condition with SUMIFS when both a start and an end date are required.

When to use SUMIFS instead

SUMIF handles one condition. SUMIFS handles multiple conditions, such as a region and a salesperson together. One detail to remember is that their argument order differs: SUMIF starts with the criteria range, while SUMIFS starts with the range to sum, followed by pairs of criteria ranges and criteria.

For example, assume A2:A5 lists East, East, West, East; B2:B5 lists Ana, Ben, Ana, Ana; and C2:C5 contains 500, 300, 250, 450. With regions in A2:A5, salespeople in B2:B5, and sales in C2:C5, =SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Ana") returns 950, adding 500 and 450. Use SUMIFS when every condition must be true for a row to count.

If you need either of two values in the same field, add two SUMIF results. With categories in A2:A5 (Food, Rent, Transport, Food) and amounts in B2:B5 (85, 1200, 40, 65), =SUMIF(A2:A5,"Food",B2:B5)+SUMIF(A2:A5,"Transport",B2:B5) returns 190. This is an OR calculation: it includes Food or Transport. For a long list of alternatives, a more structured approach may be easier to maintain than a formula with many repeated SUMIF calls.

Common SUMIF problems and fixes

The result is zero: Check for spelling differences, extra spaces, the wrong criteria range, or numbers stored as text. Confirm that the condition actually appears in the cells being checked.
The result is too high or too low: Make sure sum_range lines up with range. If the category range starts at row 2 but the amount range starts at row 3, Excel pairs the wrong records.
A comparison does not work: Put a typed operator and value together as a criterion, or join the operator to a cell reference with &. A reference by itself does not mean “greater than the value in this cell.”
A date is missed: Check that the source contains real date values, and verify the year and boundary logic. A date displayed in a familiar format can still be text.
The formula shows an error after copying: Review cell references. If the criteria range or sum range must remain fixed while copying a formula, use absolute references such as $A$2:$A$100.

Excel formulas shown here use commas between arguments, as in a standard Microsoft 365 installation with English-style list separators. Some regional settings use semicolons instead; if Excel rejects a correctly written formula, check the list separator configured for your locale. The function name and the order of its arguments do not change.

Frequently Asked Questions

Can SUMIF add cells that contain a word?
Yes. Use the text as the criterion for the range Excel should check, and specify a separate sum_range if the values to add are in another column. Use wildcards when only part of the text needs to match.

What is the difference between SUMIF and SUMIFS?
SUMIF tests one condition; SUMIFS can test several conditions at once. SUMIF places the criteria range first, while SUMIFS places the sum range first. That different argument order is a frequent source of mistakes when switching between them.

Can SUMIF add values for a date range?
It can apply one date boundary, such as on or after a start date. To include both a start and an end boundary, use SUMIFS with one condition for each boundary. Using the first day of the next period as an exclusive end is a dependable way to cover a whole month.

Does SUMIF distinguish uppercase and lowercase text?
No. SUMIF text criteria are not case-sensitive, so capitalization alone will not separate entries. If uppercase and lowercase must be treated differently, SUMIF by itself is not the right tool for that distinction.

Why does SUMIF return zero when matching entries are visible?
Often the cells contain extra spaces, inconsistent text, or numbers stored as text, or the formula is checking the wrong column. Inspect the underlying values and confirm the range and sum_range correspond row by row.

Choosing a dependable formula

Start by identifying the cells that contain the condition and the cells that contain the amounts to add. Use SUMIF for one condition, a cell reference when the condition should be adjustable, and SUMIFS when several requirements must all be met. Test the result against a few visible rows or a small hand-calculated total, especially when dates, imported text, or copied formulas are involved. Clear ranges and consistent data usually matter more than a complicated formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.