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