TOP
# Selective cell summation (SUMIFS)

## Description

## How do these features work?

## Use of complex conditions

### Related Articles:

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

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 )

ie:

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

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:

- SUMIFS(F2:F12 ; C2:C12 ; I2 ; B2:B12 ; "k*" ) - will calculate revenue for regions whose names begin with the letter "k"
- SUMIFS(F2:F12 ; C2:C12 ; I2 ; B2:B12 ; "????" ) - will calculate revenue for regions whose name consists of 4 letters
- SUMIFS(F2:F12 ; D2:D12 ; ">50" ; B2:B12 ; H2 ) - will calculate the revenue for the given region, if the sales volume was more than 50 kg
- SUMIFS(F2:F12 ; C2:C12 ; I2 ; A2:A12 ; ">10.08.2012" ) - will calculate the revenue for a certain product, if the sale took place after August 10, 2012.

- 1. What is VBA, basic concepts.
- 2. Code Debugger
- 3. Working with worksheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties
- 6.1. Data types (Variables)
- 6.2. Data types (Continued)
- 7.1. Conditions
- 7.2. Conditions (Continued)
- 8.1. Loops
- 8.2. Loops (Continued)
- 9. Procedures and functions
- 10. Dialog boxes (Dialog boxes)
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls
- 12.3. Controls (Continued)
- 12.4. Controls (Exercises)
- 13.1. Arrays
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. What is SQL? SQL essentials.
- 2. Select data from a database (SELECT)
- 3. Sorting the result records (ORDER BY)
- 4. Filter records (WHERE)
- 5. Using wildcards (LIKE)
- 6. Computed (calculated) columns
- 7. Aggregate functions (MIN, MAX, AVG etc.)
- 8. Grouping of the resulting set (GROUP BY)
- 9. Subqueries
- 10. Combining records from two tables (INNER JOIN)
- 11. Outer join (LEFT, RIGHT, FULL JOIN)
- 12. Combining the resulting sets (UNION)
- 13. Insert new records (INSERT INTO)
- 14. Create a new table (CREATE TABLE)