Table of Contents

## SUMIFS, what's for?

When you have a list of data and you need to do the sum of few cells of this list, the SUMIFS function is the one to use.

Well, in fact, there is 2 functions to do this:

- SUMIF
- SUMIFS

## SUMIF vs SUMIFS

There is a big difference between SUMIF and SUMIFS. It's the position of the column to sum.

The function **SUMIF** is

=SUMIF(Range1,Criteria1,Column to sum)

The function **SUMIFS** is

=SUMIFS(Column to sum,Range1,Criteria1,Range2,Criteria2,...)

- With
**SUMIFS**, the column to sum is**the first argument** - With the function
**SUMIF**, the column to sum is**the last one**.

## Exercise with SUMIFS

What is the amount of sales for the customer from **Germany** for the **Drink?**

- First, we select the column to sum (the column E)

=SUMIFS($E$2:$E$263

- Then, we select the first range of criteria (the column Country)

=SUMIFS($E$2:$E$263,$B$2:$B$263

- And the first criteria

=SUMIFS($E$2:$E$263,$B$2:$B$263,"Germany"

- After, we select the second range of criteria and the second value

=SUMIFS($E$2:$E$263,$B$2:$B$263,"Germany",$D$2:$D$263,"Drink")

And the result is

## Explanation of the result

In fact, you can easily check this result by filter your data on the country = "Germany" and Category = "Drink".

## Select between 2 dates

The best reason to use the SUMIFS function is to do a selection between 2 dates. To do this we must consider

- One selection on a date greater than (first date)
- Another selection on a date lower than (last date)

So in the same previous data, if we want all the sales between the 01/01/2017 and the 31/01/2017, the formula is

For the sum

=SUMIFS($E$2:$E$263

And for the first criteria

=SUMIFS($E$2:$E$263,$A$2:$A$263,">=01/01/2017")

And for the second criteria **IS THE SAME COLUMN**

=SUMIFS($E$2:$E$263,$A$2:$A$263,">=01/01/2017",$A$2:$A$263,"<01/02/2017")

Look at the second criteria, the logical sign is **<01/02/2017**. But you can also write the last day of January and change the test like this **<=31/01/2017**

As you can notice here, the reference of the column is written with the name of the column. This is because we have inserted the data in a Table.