How to calculate the total value based on multiple conditions in Excel

To calculate conditional sum in Excel, it is impossible not to mention the special calculation function that is SUMIF and SUMIFS function. In this article, Software Tips will show how to use conditional sum function. Please follow the following article.

How to calculate the total value based on multiple conditions in Excel Picture 1

Sumif function - Syntax and usage

Function structure: = SUMIF (range, criteria, [sum_range])

Inside:

1. SUMIF : The function name used to sum values ​​based on criteria .
2. Range : The area of ​​the cells evaluated according to the condition you given.
3. Criteria : The condition you put in to sum.
4. Sum_range : Optional value, sum cells if satisfying conditions, if no sum_range is entered , Excel will understand the area to be summed is the range of cells evaluated under range condition .

Usage and examples:

For example, you have the following worksheet and need to add the total number of cement (column E) that is transported by car (Column F).

You use the following formula: = SUMIF (F2: F23, "car", E2: E23) and get the following result:

How to calculate the total value based on multiple conditions in Excel Picture 2

Sumifs function - Syntax and usage

Function structure: = SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2], .)

Inside:

1. SUMIFS : Function name used to sum values ​​based on multiple criteria1, criteria2… criteria.
2. Sum_range : Required value. Sum cells if the condition is met.
3. Criteria_range1 : Required value. The area of ​​the cells is evaluated according to the condition that you give.
4. Criteria1 : Required value. The first condition must meet to sum.
5. Criteria_range2 : Optional value. The area of ​​the cells is evaluated according to the condition that you give.
6. Criteria2 : Optional value. The th condition must meet to sum.

Note that the SUMIFS function operates in the logical AND expression, that is, all the conditions must be met in order to sum the cell.

Now let's see how the SUMIFS function works with the two conditions in the following example:

How to calculate the total value based on multiple conditions in Excel Picture 3

Formulated as spreadsheets, Dexterity Software requirements calculated total cement production by means automobiles of the day 07-09-2018 . And Excel finds a unique value that satisfies the two requirements and returns a value of 30.73 .

Notes when using the SUMIF and SUMIFS functions

Note 1 : Logical expressions compared to operators must be enclosed in quotation marks "".

For example, with the same worksheet above, you need to sum the cells with values ​​greater than or equal to 32, the formula will be applied as follows:

How to calculate the total value based on multiple conditions in Excel Picture 4

Note 2 : Same as above if you find the condition by date or text format, the condition must be enclosed in quotation marks "".

For example, you need to calculate the total amount of goods exported on September 18, 2018.

How to calculate the total value based on multiple conditions in Excel Picture 5

Above are instructions and some specific examples when using SUMIFF and SUMIFS functions to calculate the total value on many conditions.

Good luck!