SUMIFS Function in Excel: Sum With Multiple Criteria

SUMIFS adds numbers that pass two or more tests at once. Write =SUMIFS(D2:D11,A2:A11,"Acme",B2:B11,"Unpaid") and Excel adds the amounts in D2:D11 only on rows where the client is Acme and the status is Unpaid. The sum range comes first, then each test range with its test.

This page shows the syntax, date windows, wildcards, OR logic, the difference from SUMIF, and the errors that give a wrong total.

TL;DR

• The pattern is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The range you want to add goes first. In SUMIF it goes last.
• Every condition must be true for a row to count. That is AND logic.
• All the ranges must be the same height and width, or the formula returns #VALUE!.
• For a date window, use the same date column twice: ">="&H2 and "<="&I2.
• SUMIFS has no built-in OR. Add two SUMIFS together, or pass an array constant such as {"Unpaid","Overdue"} and wrap the formula in SUM.
• Text tests are not case sensitive and accept the wildcards * and ?.

Table of Contents

• The sample data used on this page
• SUMIFS syntax and arguments
• SUMIFS examples, from two conditions to date windows
• SUMIFS with OR logic
• SUMIF versus SUMIFS
• Which Excel versions have SUMIFS
• SUMIFS errors and wrong totals
• What learners get wrong
• A worked job: the month-end chase list
• Practice SUMIFS in the browser
• FAQ
• Sources

The sample data used on this page

The sheet is a small freelancer's invoice log. Column A is the client, B is the status, C is the due date and D is the amount. Columns G to I hold three control cells that some formulas read.

SUMIFS syntax and arguments

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Rules to remember:

1. Every criteria range must be the same size and shape as the sum range. Five rows against four rows returns #VALUE!.
2. A row is added only when it passes all the tests.
3. Operators such as > and <> live inside the quotes with the value. When the value is in a cell, join with an ampersand: ">="&H2.
4. Text tests ignore upper and lower case.

SUMIFS examples, from two conditions to date windows

Sum with two conditions

Acme invoices that are still unpaid:

=SUMIFS(D2:D11,A2:A11,"Acme",B2:B11,"Unpaid")

It returns 2500. Only row 4 has Acme in column A and Unpaid in column B. Row 2 is Acme but Paid, and row 3 is Unpaid but Brightly, so neither counts.

Sum with a number test and a text test

You can mix kinds. Unpaid invoices larger than 2000:

=SUMIFS(D2:D11,B2:B11,"Unpaid",D2:D11,">2000")

It returns 5900, which is 2500 plus 3400. It is fine for the sum range and a criteria range to be the same column. Excel evaluates each test first and then adds.

Sum between two dates

A date window needs two tests on the same column. Both limits are in cells here: H2 holds 2026-02-01 and I2 holds 2026-02-28.

=SUMIFS(D2:D11,C2:C11,">="&H2,C2:C11,"<="&I2)

It returns 5950. The February invoices are 1850 (row 3), 2500 (row 4) and 1600 (row 8). Both the first and last day of the window are included, because the operators are >= and <=. To keep a limit out, use > or <. If you would rather not use control cells, build the dates inline with DATE: ">="&DATE(2026,2,1). That form is safe across regional date settings.

A frequent slip is writing the whole test inside the quotes, such as ">=H2". Excel then compares against the text "H2", not the date in the cell, and the total comes out wrong, usually 0. The ampersand is what joins the operator to the cell's value.

Sum with three conditions

Add another pair for every extra test. Open money for the client in G2 (Acme) due on or after the From date, excluding paid invoices:

=SUMIFS(D2:D11,A2:A11,G2,C2:C11,">="&H2,B2:B11,"<>Paid")

It returns 4100. Row 4 (2500, unpaid, due 28 February) and row 8 (1600, overdue, due 1 February) pass all three tests. The January Acme invoice fails on date and on status.

Sum with a wildcard criterion

Wildcards match part of a text value. * stands for any run of characters and ? for one character. All invoices from clients whose name starts with B that are not paid:

=SUMIFS(D2:D11,A2:A11,"B*",B2:B11,"<>Paid")

It returns 2750, which is Brightly's unpaid 1850 plus its overdue 900. To catch names that contain a fragment, put stars on both sides. For more on the symbols and how to match a real star, see the wildcard characters guide.

Sum with a "not equal to" test

<> excludes a value. Everything not billed to Acme:

=SUMIFS(D2:D11,A2:A11,"<>Acme")

It returns 15250, the 23550 total less Acme's 8300. This is the same as a SUM minus a SUMIFS, but shorter, and it keeps working when new clients appear.

SUMIFS with OR logic

SUMIFS joins its tests with AND. When you need either/or, there are two clean ways.

Add separate SUMIFS. Acme invoices that are unpaid or overdue:

=SUMIFS(D2:D11,A2:A11,"Acme",B2:B11,"Unpaid")+SUMIFS(D2:D11,A2:A11,"Acme",B2:B11,"Overdue")

It returns 4100 (2500 + 1600). This is the easiest to read and to audit.

Use a list of options. An array constant makes SUMIFS run once for each item, and SUM adds the results:

=SUM(SUMIFS(D2:D11,A2:A11,"Acme",B2:B11,{"Unpaid","Overdue"}))

It also returns 4100. The curly brackets hold the options for one criterion. This works when the OR sits on one criteria range only. If the OR spans two different columns, such as "Acme, or anything overdue", it cannot be done this way. Use SUMPRODUCT instead, or build a helper column.

The same trick works for several clients: =SUM(SUMIFS(D2:D11,A2:A11,{"Acme","Delta"})) returns 14450 (8300 for Acme plus 6150 for Delta).

SUMIF versus SUMIFS

A third option is SUMPRODUCT, which handles conditions SUMIFS cannot, at the cost of being harder to read:

=SUMPRODUCT((A2:A11="Acme")*(B2:B11="Unpaid")*D2:D11)

It returns 2500, the same as the first example. Use it for OR across different columns or for tests that need functions, and use SUMIFS for the rest.

Which Excel versions have SUMIFS

SUMIFS arrived in Excel 2007, so it works in Excel 2010, 2013, 2016, 2019, 2021 and Microsoft 365, in Excel for the web and in Excel for Mac. An Excel 2003 file cannot use it. Nothing about the formula changes between versions. The array constant shown for OR needs no special entry in any of them, because SUM finishes the work.

SUMIFS errors and wrong totals

What learners get wrong

Between 26 September and 3 November 2026, learners made 5,538 first attempts across our SUMIFS lesson, and 59.5% were right. These counts are first checks of a screen in a page view, not people. The stored wrong answers fall into a few patterns.

A missing pair, or no quotes on an operator. The hardest screen asks for the total number of eyes for animals with exoskeletons and more than 6 legs. It had 710 first attempts and 37.9% were right. The accepted answer is =SUMIFS(D2:D6,B2:B6,"exoskeleton",C2:C6,">6"). Twenty-two first attempts were =SUMIFS(D2:D6,B2:B6,"Exoskeleton",C2:C6,>6), with no quotes around >6. Fourteen turned the test round with "<6". Seven used a bare 6, as in =SUMIFS(D2:D6,B2:B6,"Exoskeleton",C2:C6,6), which means exactly 6, not more than 6. Seven more tested the wrong column, =SUMIFS(D2:D6,B2:B6,"Exoskeleton",D2:D6,">6"). Read each pair as "this column, this test".

Operator and range muddled. Another screen asks for the legs of animals with 2 eyes and more than 4 legs. It had 512 first attempts and 44.5% were right. The accepted answer is =SUMIFS(C2:C6,D2:D6,2,C2:C6,">4"). Nineteen first attempts were =SUMIFS(C2:C6,D2:D6,">2",C2:C6,">4"), turning "2 eyes" into "more than 2 eyes". Eighteen were =SUMIFS(C2:C6,">4",D2:D6,2), a test with no range in front of it. Thirteen put quotes around the 2, giving =SUMIFS(C2:C6,">4",D2:D6,"2"), with the same missing range. Every criteria is preceded by the range it applies to, with no exceptions.

Reading a formula with a wildcard. One screen shows =SUMIFS(C2:C6, A2:A6, "<>*p") and asks what it returns. It had 494 first attempts and 42.3% were right, and the answer is 18. The test means "not ending with the letter p". The star stands for any text before the p, so every name that ends in p is excluded and everything else is added. A good habit is to find which rows fail the test first, and add what is left.

Our SUMIFS practice lesson had 2,318 first attempts, 67.9% right. Its hardest screen, 218 first attempts at 51.4%, concerns a creek that spans two towns. One stored wrong answer filters on Greene only, while the creek spans two towns. Check that your tests cover every place the question names.

A worked job: the month-end chase list

The owner wants to know how much is still outstanding, who owes it, and how much of it is already overdue. Use the invoice log above.

1. Find the total still open. Anything not Paid is open: =SUMIFS(D2:D11,B2:B11,"<>Paid"). It returns 11500. That is 23550 minus the 12050 that has been paid.
2. Break it down by client. List the clients in G2:G5 (Acme, Brightly, Cobalt, Delta). In H2 enter =SUMIFS($D$2:$D$11,$A$2:$A$11,G2,$B$2:$B$11,"<>Paid") and fill down. The dollar signs keep the ranges fixed as you copy; the absolute reference guide explains them.
3. Check the breakdown. Add the column with =SUM(H2:H5). It must equal the 11500 from step 1. A different answer means a client is missing from the list.
4. Pull out what is overdue. =SUMIFS(D2:D11,B2:B11,"Overdue") returns 2500, the 900 from Brightly plus the 1600 from Acme. That is the number for the chasing email.

Practice SUMIFS in the browser

The exercise above this article is Practice Lesson 30. Type the formula yourself and the lesson checks it. Then try Practice Lesson 61, which asks for totals from a longer table with wildcards in the criteria. The first two finished lessons on the site are free, then a pass opens the rest. If you have not used the one-condition version yet, do the SUMIF guide first. For a broader view of Excel's tools, see the functions cheat sheet.

FAQ

How do I use SUMIFS with multiple criteria in Excel?

Start with the range to add, then give each condition as a range and a test: =SUMIFS(D2:D11,A2:A11,"Acme",B2:B11,"Unpaid"). A row counts only when it passes every test. Keep all the ranges the same size.

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first. Both give the same result for a single test. SUMIFS is the one to learn, because adding a second test later is just another pair.

Can SUMIFS use OR instead of AND?

Not directly. Every test must be true. For an OR on one column, add two SUMIFS, or write =SUM(SUMIFS(D2:D11,B2:B11,{"Unpaid","Overdue"})). For an OR across different columns, use SUMPRODUCT or a helper column.

How do I sum between two dates with SUMIFS?

Test the same date column twice: =SUMIFS(D2:D11,C2:C11,">="&H2,C2:C11,"<="&I2). The first test sets the start and the second sets the end. Both ends are included with >= and <=. Use > or < to leave an end out.

Why does SUMIFS return #VALUE!?

The most common reason is that the sum range and a criteria range are different sizes, for example D2:D11 against A2:A10. Every range must have the same number of rows and columns. Also check that a criteria does not contain a stray character.

Is SUMIFS case sensitive?

No. "acme", "Acme" and "ACME" all match the same cells. If you need a case sensitive total, use SUMPRODUCT with EXACT. For most business data, being case insensitive is what you want.

Can SUMIFS use wildcards?

Yes. In text tests, * matches any run of characters and ? matches one. "B*" matches every name that starts with B, and "*a*" matches every name that contains an a. To match a real star or question mark, put a tilde in front.

Sources

• SUMIFS function, Microsoft Support
• SUMIF function, Microsoft Support