↑ Return to Condition

SUMIFS

Principle

The SUMIFS function is constructed as the COUNTIFS function, in sense that you can realize a sum over a part of your data by selecting one or more criteria.

There’s no difference between these two functions except the first parameter; the range of cells that contains cells to add. Next parameters are still the range of cells containing the criteria and the criteria.

There in lies the difference with the function SUMIF. With the SUMIF function you must first select your range of cells, then your criteria and finally the range of cells to sum.

With the SUMIFS function, you must first select the range of cells to sum and then one or more criteria. You can put up to 256 selection criteria in a function SUMIFS.

In this page, we will resume all examples of the function COUNTIFS. Instead of counting the number of rows that match our criteria, we will make additions.

Construction

In the page on the function COUNTIFS, we saw how to determine the number of married men. in our list

We will now determine how many children have these married men?


They just take over the function COUNTIFS (double-click in the chart below) and add in the first position the reference of the column of children. Other criteria used for selection, remain the same. The formula is therefore

=SUMIFS($F$2:$F$20,$C$2:$C$20,H2,$E$2:$E$20,H3)

Greater than / Lower than




Previously, we have found the number of orders that are greater or equal than 1500. With this selection, we want to calculate the sum of theses orders. We will again take over the function COUNTIFS (double-click to display the formula) and add the column F (column total) in the first position in the formula SUMIFS. The formula is

=SUMIFS($H$2:$H$5000;$B$2:$B$5000;$J2;$H$2:$H$5000;">=1500")

Variable date

Finally, we will start from the example that was used to calculate the number of orders over a period of time. This time, we will calculate the amount of orders for a particular client (in our example, Mr. Smith).

=SUMIFS($H$2:$H$5000;$B$2:$B$5000;K$1;$A$2:$A$5000;”>=”&K$2;$A$2:$A$5000;″<=″&K$3)

Again, to build your SUMIFS function must take over the function COUNTIFS and add the column to be added in the first position of your settings.

Replace the name of the client (by Garcia, Charlton, …) or also the month and the year to change the result in the worksheet.

=SUMIFS($H$2:$H$5000;$B$2:$B$5000;K$1;$A$2:$A$5000;”>=”&DATE($K$2;$K$3;1);
$A$2:$A$5000;″<″&DATE($K$2;$K$3+1;1))





Leave a Reply

%d bloggers like this: