# Function SUMIFS

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 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.

## Related posts

Permanent link to this article: https://www.excel-exercise.com/sumifs/