Selective cell summation (SUMIFS)


We often need to sum values in tables. For this, there is a standard SUM function in Excel that can sum a whole range of values. However, sometimes we need to perform a selective summation. For such cases, there are improved functions SUMIF (summing by one criterion) and SUMIFS (for summing by many criteria).

How do these features work?

Let's consider the following example:

We have a table of fruit sales by region. Suppose we need to find out information about sales revenue in a separate region and for a separate product. To do this, select the SUMIFS formula from the section Mathematical , and write the arguments as follows:

The syntax of the formula is as follows:

=SUMIFS(The range_which_is_summarized ; range_for_condition_1 ; condition_1 ; range_for_condition_2 ; condition_2 )


=SUMIFS(Revenue, UAH ; Product name ; Pears ; Sales region ; Lviv )

Use of complex conditions

We draw your attention to the fact that it is worth fixing cell ranges (with a dollar sign $ or press the F4 key), because when copying, they will move and the formula will give an incorrect result.

Also, with selective summation, we can use more complex constructions:

Related Articles: