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
- With COUNTIF you can count the rows for one criterion
- With COUNTIFS you can count the rows for 256 criteria
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)
- 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
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
And for the first criteria
And for the second criteria ON THE SAME COLUMN
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.