To sum values that meet several conditions in Excel, use SUMIFS: it applies all specified criteria at once, so matching rows must meet every condition. With products in A2:A8, regions in B2:B8, dates in C2:C8, and sales in D2:D8, this example totals Coffee sales in the East during January 2026: =SUMIFS(D2:D8,A2:A8,"Coffee",B2:B8,"East",C2:C8,">="&DATE(2026,1,1),C2:C8,"<"&DATE(2026,2,1)), which returns 190 for the sample data below.
What SUMIFS does
SUMIFS adds numbers from a sum range only when corresponding cells satisfy the criteria you provide. It is usually the simplest choice for a filtered total based on more than one field—for example, sales for a particular product, region, and date period. Unlike manually filtering a table and adding visible values, the formula recalculates when source values or criteria change.
The examples use this small dataset. Dates in column C must be real Excel dates, not text that merely looks like a date.
| Row | Product (A) | Region (B) | Date (C) | Sales (D) |
|---|---|---|---|---|
| 2 | Coffee | East | Jan 5, 2026 | 120 |
| 3 | Tea | East | Jan 12, 2026 | 80 |
| 4 | Coffee | West | Jan 15, 2026 | 150 |
| 5 | Coffee | East | Feb 3, 2026 | 200 |
| 6 | Tea | West | Feb 10, 2026 | 90 |
| 7 | Coffee | East | Feb 14, 2026 | 130 |
| 8 | Coffee | East | Jan 20, 2026 | 70 |
In the opening calculation, the qualifying rows are 2 and 8: both are Coffee, both are East, and both dates fall in January. Their sales total 120 + 70 = 190. The remaining rows fail at least one condition.
How to build a SUMIFS formula
The syntax is SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...). The sum range comes first, followed by pairs of a range to test and the rule that range must meet. Each additional pair narrows the matches: conditions are combined as AND, not OR.
- Choose the numeric cells to add as the sum range.
- For each condition, select the cells containing the field you want to test.
- Enter a criterion such as a text value, a number, a comparison, or a reference to a cell containing the criterion.
- Check that every criteria range covers the same rows and has the same dimensions as the sum range.
For the sample, the sum range is D2:D8. Product, region, and date are tested in A2:A8, B2:B8, and C2:C8. If the source data grows, convert it to an Excel table using Insert > Table. Structured references can make formulas easier to read and can expand as rows are added, but the basic range-based formula is often easier to audit when learning the function.
Use AND criteria for combined conditions
A common request is to total one product in one region. With products in A2:A8, regions in B2:B8, and sales in D2:D8, this formula adds only Coffee sales from the East: =SUMIFS(D2:D8,A2:A8,"Coffee",B2:B8,"East"). The result is 520, calculated from 120 + 200 + 130 + 70. The Tea sale in the East is excluded because it does not match the product criterion.
Criteria can refer to cells rather than fixed text. For example, if H2 contains Coffee and H3 contains East, the formula can use those cells as its criteria. This makes a small summary area interactive: change a selection and the total updates without editing the formula. For a criterion stored as text, enter the text without adding wildcard characters unless you want a partial match.
Comparison operators need quotation marks inside the criterion. To join an operator to a value in another cell, concatenate them with &. If H2 contains a minimum sales amount, for instance, use a criterion like ">="&H2. When the range being tested is also the range being summed, a threshold can select which values contribute. With sales in D2:D8, this adds sales of at least 100: =SUMIFS(D2:D8,D2:D8,">=100"). The result is 600 (120 + 150 + 200 + 130).
Sum a date range correctly
For date periods, use a lower bound and an upper bound on the same date range. An inclusive start and exclusive next-period boundary avoids missing times attached to dates. With dates in C2:C8, sales in D2:D8, and Coffee in A2:A8, this totals Coffee sales in January 2026: =SUMIFS(D2:D8,A2:A8,"Coffee",C2:C8,">="&DATE(2026,1,1),C2:C8,"<"&DATE(2026,2,1)). The result is 190. The less-than-February-1 test includes every time on January 31 if the cells contain timestamps as well as dates.
You can instead put the start date in H2 and the first day of the next month in H3. Then join each comparison operator to its cell reference. This is useful for changing the reporting period without rewriting a formula. Avoid typing dates in an ambiguous format when a workbook may be opened with different regional settings; DATE(year,month,day) or a cell containing a valid Excel date is more reliable. If a date criterion appears not to match, check whether the source values are true dates rather than text.
Handle OR conditions and partial matches
Several criteria pairs in one SUMIFS formula mean AND: a row has to satisfy all of them. If the request is “Coffee or Tea in the East,” the product test is OR while the region test still applies. One clear method is to add separate SUMIFS results. With product in A2:A8, region in B2:B8, and sales in D2:D8, use: =SUMIFS(D2:D8,A2:A8,"Coffee",B2:B8,"East")+SUMIFS(D2:D8,A2:A8,"Tea",B2:B8,"East"). The result is 600: 520 for East Coffee plus 80 for East Tea.
For a compact alternative, SUMPRODUCT can combine Boolean tests. With the same layout, this formula adds East sales when the product is Coffee or Tea: =SUMPRODUCT((B2:B8="East")*((A2:A8="Coffee")+(A2:A8="Tea"))*D2:D8). It returns 600. Multiplication represents AND, while addition between tests represents OR. Be careful if the OR tests can both be true for the same row: adding the tests can count that row twice. Separate SUMIFS formulas are often easier to inspect and maintain.
Wildcards are useful when a text field is not an exact match. An asterisk matches any number of characters, while a question mark matches one character. With product names in A2:A8, region in B2:B8, and sales in D2:D8, this adds East sales for names containing “cof”: =SUMIFS(D2:D8,A2:A8,"*cof*",B2:B8,"East"). It returns 520 for the sample because the Coffee entries match. Wildcard matching is not case-sensitive in ordinary Excel criteria. To match a literal asterisk or question mark, prefix the character with a tilde.
SUMIFS versus a worksheet filter
A formula criterion is not the same as applying a filter from the Data tab. SUMIFS evaluates every row in its specified ranges, including rows hidden by an AutoFilter. Hiding nonmatching rows on screen does not make them disappear from the calculation. That behavior is helpful when the goal is a stable criteria-based summary, but it surprises people who expect a total to change as they filter a list.
If the requirement is specifically “sum only visible rows that also meet conditions,” add a helper column. Assume product, region, and sales are in A2:A8, B2:B8, and D2:D8, and put this in E2, then fill it down through E8: =SUBTOTAL(103,A2). It returns 1 for a visible row with a nonblank product and 0 when that row is filtered out. With a header row and AutoFilter applied, total visible East Coffee rows using: =SUMIFS(D2:D8,A2:A8,"Coffee",B2:B8,"East",E2:E8,1). Because the sample has no active filter, the result is 520. Filtering the list can change this total. Make sure the helper references a field that is populated in every relevant row; otherwise visible rows with a blank in that field are not flagged. Function number 103 also ignores manually hidden rows.
Common errors and how to fix them
- Wrong total range order: In
SUMIFS, the sum range is first. This differs fromSUMIF, which places its criteria range before the optional sum range. - Ranges with different sizes: Use corresponding ranges such as A2:A100 and D2:D100, not a criteria range ending at row 100 and a sum range ending at row 99.
- Numbers stored as text: A value that looks numeric but is stored as text may not add as expected. Check the source data and convert it to numbers when appropriate.
- Extra spaces or inconsistent labels: “East” and “East ” may not match as expected. Clean imported text or use a deliberate wildcard criterion where partial matching is suitable.
- Dates stored as text: Date comparisons work reliably when the cells contain Excel date serial values. Convert imported text dates before relying on date boundaries.
- Expecting a worksheet filter to affect the formula: Ordinary
SUMIFSincludes hidden matching rows. Use a visibility helper when the total must follow the displayed rows.
When a total is unexpected, test one condition at a time. Temporarily remove criteria pairs or use a small subset of rows to identify whether the issue is a range boundary, a data type, or the logic of AND versus OR. Also confirm that the formula was copied with the intended references; absolute references such as $D$2:$D$100 keep a range fixed when copied, while relative references move.
Choosing the right formula
Use SUMIFS when all conditions must hold, which covers most multi-field totals. Use multiple SUMIFS formulas added together for straightforward OR choices, especially when the alternatives are easy to list. Choose SUMPRODUCT when you need to combine Boolean tests in a single expression or when the logic is more involved, but document it carefully because its logic can be less obvious to a reader. For totals that must follow visible filtered rows, use a visibility helper in combination with SUMIFS, or consider a PivotTable when you need a reusable interactive summary by several categories.
These functions are available in current desktop Excel for Microsoft 365 on Windows, as well as many earlier Excel releases. The examples use ordinary worksheet formulas, so they also work in Excel for Mac and Excel for the web; ribbon locations and interface details can vary. You can enter a formula in the formula bar or directly in a cell, then press Enter. On Windows, Ctrl+Shift+Enter is not needed for these examples. On Mac, formulas are entered normally with Return; keyboard shortcut behavior can depend on the keyboard settings.
Frequently Asked Questions
Can SUMIFS use more than two criteria?
Yes. Add another criteria-range and criterion pair for each additional condition, keeping the ranges aligned with the sum range. For example, product, region, date, and salesperson can all be tested in one formula.
Does SUMIFS use AND or OR between criteria?
It uses AND. A matching row must meet every criteria pair. For OR logic, add separate SUMIFS results or build a carefully checked expression with SUMPRODUCT.
Why does my SUMIFS total include rows hidden by a filter?SUMIFS evaluates its full referenced ranges and does not automatically exclude hidden rows. Add a SUBTOTAL helper column and include that helper as a criterion when the result must reflect visible rows.
Can I use a date range as a SUMIFS criterion?
Yes. Use one criterion for dates on or after the start and another for dates before the next period. This gives a reliable month or year boundary and includes times within the dates being covered.
What is the difference between SUMIF and SUMIFS?SUMIF is designed for one criterion, while SUMIFS supports multiple criteria. Their argument order differs: SUMIFS starts with the sum range, so check the syntax when switching between them.
Related
- Excel Shortcut for Inserting a Row on Windows and Mac
- Excel Column Selection Shortcut: Ctrl+Space and More
- Excel Fill Colour Shortcut: Apply a Cell Colour
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Audit Working Papers Excel Templates ExplainedYes. Audit working paper Excel templates are available for organizing procedures, evidence, calculations, and review notes, but an…
- How to Create a Signature in OutlookTo create a signature in Outlook, open signature settings, write and format your sign-off, then choose whether Outlook…
- Google Sheets Monthly Budget Template: Build One That WorksA Google Sheets monthly budget template is a spreadsheet for listing planned income and expenses, recording what you…
- Group and Subtotal Rows in Excel: A Practical GuideIn Excel, group rows to collapse and expand related detail, and use Subtotals to insert calculated summary rows…
- Insert a Row in an Excel PivotTable: Steps and FixesTo add a row to an Excel PivotTable, click inside it, select PivotTable Analyze > Field List, then…
- Google Sheets INDEX MATCH Formula: How to Use It RightWith item names in A2:A6, prices in B2:B6, and the item to find in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,0)) to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.