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. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"