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:

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/


Leave a Reply

Your email address will not be published.