SUMIFS: Add Up Only the Numbers You Care About

Have You Ever Added Up Numbers by Hand?

Imagine a list of sales. Some belong to Anna, some to Ben, some were made in the north and some in the south. Your boss asks: "How much did Anna sell in the north?"

You could filter the list, select the numbers and read the total at the bottom of the screen. Then your boss asks about Ben, and you start over. And over again.

SUMIFS does this for you with one formula. Change the name, and the total updates by itself.

The name sounds technical, but it is easy to remember: SUM means add up, and IFS means "if these conditions are true".

Is SUMIFS Useful for Me?

Probably, if you ever need to answer questions like these:

• How much did we spend on food in March?
• What are the total sales for one salesperson?
• How many hours did one person work on one project?
• What is the total of all unpaid invoices from one customer?

If the question has the word "total" in it and a "for" or "in" or "from" after it, SUMIFS is probably the answer.

See It in Action

Here is a small sales list. Column A has the salesperson, column B the region and column C the amount sold.

In cell G2 we type one formula. Excel looks for rows where the salesperson is Anna and the region is North (the orange cells in E2 and F2). Only rows 2 and 6 match (the blue cells), so Excel adds their amounts (the green cells): 200 + 100 = 300.

Notice that Anna's 300 in the south is not counted, and neither is Ben's 400 in the north. A row has to meet every condition to be included.

What you ask for Rows that match Numbers that are added

The Formula, Step by Step

Here is the formula that did it:

=SUMIFS( C2:C7, A2:A7, E2, B2:B7, F2 )

Read it from left to right as three questions. The second and third questions each come with a column to check and a condition to check it against:

Change E2 to "Ben" and F2 to "South", and the total changes to 150. You can also leave the second condition out if you only need one, or add a third, fourth or more. Just keep adding pairs of "column to check" and "condition".

Comma or Semicolon? It Depends on Where You Live

If a formula from the internet gives an error even though it looks perfect, the separator is often the reason.

The formula does the same thing in both styles. In many European countries the comma is the decimal mark (2,5 instead of 2.5), so Excel uses the semicolon between the pieces. If you get an error, swap every comma for a semicolon, or the other way around.

How to Use It Yourself

1. Click the empty cell where you want the total.
2. Type =SUMIFS(
3. Select the column with the numbers you want to add up, then type a comma (or semicolon).
4. Select the column for your first condition, then the cell or word it must match.
5. Repeat for every extra condition, close the bracket ) and press Enter.

Conditions Can Be Smarter Than "Equals"

So far the condition was an exact name. SUMIFS can also compare numbers and match patterns. Type the condition in quotation marks:

For example, this adds up all of Anna's sales that are bigger than 100:

=SUMIFS(C2:C7, A2:A7, "Anna", C2:C7, ">100")

Here the same column (C) is used both for adding up and for a condition. That is perfectly fine.

SUM, SUMIF and SUMIFS: What Is the Difference?

If in doubt, use SUMIFS. It works with one condition too, and you never have to switch functions when your question grows.

Common Problems and How to Fix Them

The result is 0, but you expected more

No row met all the conditions. Check the spelling, and look for extra spaces in the data or the condition cell. "Anna " (with a space) is not the same as "Anna". Also check that the condition really matches how the data is written, such as "North" versus "Northern".

The result is too big or too small

Check that the number column comes first in the formula. If the order is mixed up, Excel will add up the wrong column. Also confirm that each condition points at the right column.

Excel says there is a problem with your formula

Check the separators. If your Excel uses European settings, use semicolons (;) instead of commas (,), as explained above.

You see #VALUE!

The columns in your formula are not the same size. For example, C2:C7 together with A2:A9. Make sure every range has the same number of rows.

Some numbers are ignored

Sometimes a number is stored as text, so it looks like a number but Excel will not add it up. If the cell has a small green triangle or the number is left-aligned, convert it to a real number.

A condition with > or < does not work

Conditions that contain signs like >, < or = must be inside quotation marks, such as ">100". To use a value from a cell, join them with an ampersand: ">"&E2.

The Short Version

• SUMIFS adds up numbers, but only in rows that meet your conditions.
• The formula is =SUMIFS(add this up, check this column, for this, ...).
• The numbers to add up always come first, then pairs of "column to check" and "condition".
• A row must meet all conditions to be counted.
• Point conditions at cells so you can change the question without touching the formula.
• Use commas in American-style Excel and semicolons in European-style Excel.