Function SUMIFS

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:


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)


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


  • And the first criteria


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


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


And for the first criteria


And for the second criteria IS THE SAME COLUMN


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:

Leave a Reply

Your email address will not be published.