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:
- SUMIF : The function name used to sum values based on criteria .
- Range : The area of the cells evaluated according to the condition you given.
- Criteria : The condition you put in to sum.
- 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:
- SUMIFS : Function name used to sum values based on multiple criteria1, criteria2… criteria.
- Sum_range : Required value. Sum cells if the condition is met.
- Criteria_range1 : Required value. The area of the cells is evaluated according to the condition that you give.
- Criteria1 : Required value. The first condition must meet to sum.
- Criteria_range2 : Optional value. The area of the cells is evaluated according to the condition that you give.
- 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!
You should read it
- Calculate the total value of the filtered list in Excel
- 3 ways to calculate totals in Excel
- How to format data based on other cell conditions in Excel
- SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel
- How to use DSUM function in Excel
- How to automatically calculate and copy formulas in Excel
- How to count on multiple sheets of Google Sheets
- How to use SUMIF with 2 or more conditions in Excel
- The SUMIFS function, how to use multiple conditional calculation functions in Excel
- Practical exercise on computer rental list in Excel
- How to calculate NPV in Excel
- DATEDIF () function (calculate the total number of years, total months or total days from two given periods) in Excel