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:

Difference between SUMIF and SUMIFS

Like for the difference between COUNTIF and COUNTIFS, the difference is the number of criteria

  • With COUNTIF you can count the rows for one criterion

=COUNTIF(Range1,Criteria1)

  • With COUNTIFS you can count the rows for 256 criteria

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,Range3,...)

The construction is the same, you just add more criteria

But for SUMIF and SUMIFS, the way to write the formula is also different

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 parameter but with the function SUMIF, the column to sum is the last one.

Example of SUMIFS

Now, let's see how to use the function in an example. Let's take this worksheet where we have the sales for all our customers, by country, by category and the amount of each sales.

What is the total of sales in Germany for the drink?

Good question and the answer is easy

  • 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")

So the result is

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 ON 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. If you want to have to write the last day of the month, the test is 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.