SUMIFS in Excel: how to use it
SUMIFS adds up with several conditions. The sum range comes first, followed by the pairs of range and criteria.
Try the first exercises for freeIn the lesson Adding up with SUMIFS
What does SUMIFS do?
SUMIFS adds up numbers from the rows that meet all the conditions at once. You start with the column you're adding up, followed by the pairs: a range and what has to be in it.
Every row has to meet every pair, just like with COUNTIFS. That's how you make totals such as the revenue from laptops in Leeds, or all the spending of a department in one month.
When do you use SUMIFS?
- The revenue of one product in one region: =SUMIFS(C2:C100,A2:A100,"Leeds",B2:B100,"Laptop").
- All amounts between two dates: =SUMIFS(C2:C100,A2:A100,">="&F1,A2:A100,"<="&F2).
- The hours an employee spent on one project: =SUMIFS(D2:D200,A2:A200,"Sophie",B2:B200,"Website").
- A summary with regions in the rows and products in the columns, with one formula that you fill across the whole table.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =SUMIFS(C2:C6,A2:A6,"Leeds",B2:B6,"Laptop")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SUMIFS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SUMIFS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
sum_range | The column with the numbers you add up. With SUMIFS, it comes first. | C2:C6 |
range1 | The first column in which you check a condition. | A2:A6 |
criteria1 | What has to be in that column. | "Leeds" |
[range2, criteria2] | Optional: another pair, and so on. | B2:B6 |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
SUMIFS step by step
E1 contains =SUMIFS(C2:C6,A2:A6,"Leeds",B2:B6,"Laptop"). Only the laptops in Leeds. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type the function with a bracket, and straight away select what you're adding up: the revenue in C2:C6. Type a comma.
=SUMIFS(C2:C6, - The first pair: the regions in A2:A6, a comma and "Leeds".
=SUMIFS(C2:C6,A2:A6,"Leeds" - The second pair: a comma, the products in B2:B6, another comma and "Laptop".
=SUMIFS(C2:C6,A2:A6,"Leeds",B2:B6,"Laptop" - Close the bracket and press Enter: only the laptops in Leeds, 1200 in total.
=SUMIFS(C2:C6,A2:A6,"Leeds",B2:B6,"Laptop")
More examples
Between two dates
E4 contains =SUMIFS(B2:B5,A2:A5,">="&E1,A2:A5,"<="&E2). Only the amounts from March: from the date in E1 up to and including the one in E2. The same column appears twice, with a different condition.
With one condition
E1 contains =SUMIFS(C2:C6,B2:B6,"Monitor"). SUMIFS works with one condition too. If you always use SUMIFS, you only have to remember one order.
Over an amount in one region
E1 contains =SUMIFS(C2:C6,A2:A6,"Leeds",C2:C6,">500"). Here the revenue column is both what you add up and a condition: only the revenue figures from Leeds over 500.
Where it often goes wrong
- The order is different from SUMIF.
- All ranges must be the same size.
Practise this in the lesson Adding up with SUMIFS. The first exercises are free.
Frequently asked questions
Can I use SUMIFS with just one condition?
Yes, and it's even handy to always use the same order.