Selective summation of the cells (SUMIFS)

Often we have to sum the values in the tables. For this purpose in Excel is the standard function SUM, which can sum up a whole range of values. But sometimes we need to sum part of cells in the range by condition. For such cases exist improved functions like SUMIF (sum up by 1 criteria) and SUMIFS (sum up by many criterias).

How to work this functions?

Let’s review following example:

We have the table with sales data by regions. Suppose, we need to know information about the sales income in the each region and for each goods. For this, choose SUMIFS function from Math&Trig Tab and write arguments as following:

The syntax of SUMIFS function as follows:

=SUMIFS(Sum_range;condition_range_1;condition_1;condition_range_2;condition_2)

Namely:

=SUMIFS(Revenue;Goods_name;Pears;Sales_region;Lviv)

Pay your attention you should fix the ranges of cells by dollar sign $ or press button F4). Because while copy the function down the ranges will slide down and we will take an error.

Also, on the selective summation we can use more complicated constructions: