SUMIF.
D5What is SUMIF?
SUMIF is a spreadsheet function that adds up values in a range, but only when corresponding cells meet a specified condition. It combines the logic of SUM and IF into a single, efficient formula.
For example, if you have a sales report with regions in column A and revenue in column B, SUMIF lets you answer questions like "What is the total revenue for the West region?" without manually filtering or sorting your data.
SUMIF is available in Microsoft Excel, Google Sheets, and Apple Numbers. It is one of the most frequently used functions in business spreadsheets for reporting, budgeting, and data analysis.
SUMIF Syntax
The SUMIF function takes three arguments:
=SUMIF(range, criteria, [sum_range])- range — The range of cells to evaluate against the criteria (e.g., the column containing region names).
- criteria — The condition that determines which cells to include. This can be a number, text string, cell reference, or expression like
">100". - [sum_range] — Optional. The range of cells to sum. If omitted, the function sums the cells in the
rangeargument itself.
SUMIF Examples
These practical examples show SUMIF solving common business tasks. Each includes a natural language description you could type into FormulaAI.
1. Sum Sales by Region
Column A contains region names (North, South, East, West) and column B contains revenue amounts. You want the total revenue for the West region.
Description: "Sum all sales in column B where the region in column A is West"
=SUMIF(A:A, "West", B:B)SUMIF checks each cell in column A. Whenever it finds "West", it adds the corresponding value from column B to the total. Text criteria are case-insensitive, so "west", "West", and "WEST" all match.
2. Sum Values Greater Than a Threshold
You have a list of transaction amounts in column C and you want to sum only the transactions over $1,000.
Description: "Sum all values in column C that are greater than 1000"
=SUMIF(C:C, ">1000")When the criteria range and the sum range are the same column, you can omit the third argument. The comparison operator and value are enclosed in quotes together as a single string.
3. Sum Using a Wildcard for Partial Text Match
Column A contains product names like "Widget A", "Widget B", "Gadget X". You want the total sales (column B) for all products that start with "Widget".
Description: "Sum sales in column B for all products in column A that start with Widget"
=SUMIF(A:A, "Widget*", B:B)The asterisk (*) is a wildcard that matches any sequence of characters. You can also use a question mark (?) to match a single character. Wildcards work in SUMIF across Excel, Google Sheets, and Apple Numbers.
What is SUMIFS?
SUMIFS is the multi-criteria version of SUMIF. While SUMIF handles a single condition, SUMIFS lets you apply two or more conditions simultaneously. Only cells that meet all specified criteria are included in the sum.
Note an important syntax difference: in SUMIFS, the sum_range comes first, followed by pairs of criteria ranges and criteria. This is the opposite of SUMIF, where the sum range comes last.
SUMIFS Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- sum_range — The range of cells to sum (comes first, unlike SUMIF).
- criteria_range1 — The first range to evaluate against its criteria.
- criteria1 — The condition for criteria_range1.
- [criteria_range2, criteria2, ...] — Additional range/criteria pairs. You can include up to 127 pairs in Excel and 127 in Google Sheets.
SUMIFS Examples
1. Sum Sales by Region and Product
Column A has regions, column B has product names, and column C has revenue. You want the total revenue for "Widget" in the "West" region.
Description: "Sum revenue in C where region in A is West and product in B is Widget"
=SUMIFS(C:C, A:A, "West", B:B, "Widget")SUMIFS evaluates both conditions for every row. Only rows where column A is "West" and column B is "Widget" have their column C values added to the total.
2. Sum Values Within a Date Range
Column A contains dates and column B contains amounts. You want to sum all amounts from January 2026.
Description: "Sum amounts in B where dates in A are between January 1 2026 and January 31 2026"
=SUMIFS(B:B, A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,1,31))This uses two criteria on the same column (A) to create a date range. The & operator concatenates the comparison operator with the DATE function. This pattern works for any date range — quarters, fiscal years, or custom periods.
3. Sum with Text Wildcards and Numeric Conditions
Column A has product names, column B has quantities, and column C has unit prices. You want the total quantity for all "Pro" products (e.g., "Pro Basic", "Pro Max") where the unit price exceeds $50.
Description: "Sum quantities in B for products starting with Pro in A where price in C is over 50"
=SUMIFS(B:B, A:A, "Pro*", C:C, ">50")You can mix text wildcards and numeric comparisons in the same SUMIFS formula. The wildcard "Pro*" matches any product name starting with "Pro", while ">50" filters on price.
4. Sum Excluding a Specific Category
Column A has expense categories and column B has amounts. You want the total expenses excluding the "Travel" category.
Description: "Sum expenses in B where category in A is not Travel"
=SUMIFS(B:B, A:A, "<>Travel")The <> operator means "not equal to". You can use it to exclude specific values. Even though this example has only one criterion, using SUMIFS (instead of SUMIF) keeps the sum_range-first syntax consistent if you later add more conditions.
SUMIF vs SUMIFS vs SUMPRODUCT
All three functions can conditionally sum data, but they differ in flexibility and use cases:
| Feature | SUMIF | SUMIFS | SUMPRODUCT |
|---|---|---|---|
| Number of criteria | 1 | Up to 127 | Unlimited |
| OR logic support | No (add multiple SUMIFs) | No (AND only) | Yes |
| Wildcard support | Yes | Yes | No (use SEARCH/FIND) |
| Calculated criteria | Limited | Limited | Full array logic |
| Performance | Fastest | Fast | Slower on large data |
| Best for | Simple single-condition sums | Multi-criteria AND sums | Complex OR/calculated conditions |
Use SUMIF when you have a single, straightforward condition. It is the simplest and fastest option.
Use SUMIFS when you need multiple AND conditions (e.g., region = "West" AND quarter = "Q1"). This covers the vast majority of multi-criteria scenarios.
Use SUMPRODUCT when you need OR logic across criteria, weighted calculations, or conditions that involve computed values rather than simple comparisons.
Related Functions
The IF function family extends beyond summing. Here are related functions that follow the same pattern:
- COUNTIF / COUNTIFS — Count cells that meet one or more conditions instead of summing them. Uses the same criteria syntax as SUMIF/SUMIFS.
- AVERAGEIF / AVERAGEIFS — Calculate the average of cells that match conditions. Useful for metrics like "average order value for premium customers".
- MAXIFS / MINIFS — Find the largest or smallest value that matches conditions. Available in Excel 2019+, Excel 365, and Google Sheets.
Once you understand SUMIF and SUMIFS, these related functions become easy to learn because the argument structure is nearly identical.
Tips for Writing Better SUMIF Formulas
- Use cell references for criteria — Instead of hardcoding
"West", reference a cell likeE1. This makes your formula dynamic and reusable. - Watch the argument order — SUMIF puts sum_range last; SUMIFS puts sum_range first. This is the most common source of confusion when switching between the two.
- Use absolute references in templates — When copying formulas down a column, use
$A:$Ainstead ofA:Afor the range/sum_range to prevent shifting. - Combine with MONTH, YEAR for date grouping — Instead of date ranges, you can use helper columns with
MONTH()andYEAR()for cleaner monthly/yearly summaries. - Test with small data first — Verify your formula logic on a few rows before applying it to a large dataset. This catches criteria mismatches early.
Related Guides
- IF Formula Guide — Master conditional logic with IF, IFS, and nested IF formulas.
- VLOOKUP Guide — Look up values across columns with the classic VLOOKUP function.
- SUMIFS with Multiple Criteria — sum across several conditions, dates and wildcards.
- All formula guides — browse every guide.
- FAQ — Common questions about FormulaAI and supported formulas.