Sum several cells based on specified conditions.
Grammar
SUMIF（range,criteria,sum_range）
Range is the cell range used for conditional judgment.
Criteria A criterion that determines which cells will be added and can be a number, expression, or text.
For example, the condition can be expressed as 32, 32, >32, or apples.
Sum_range is the actual cells that need to be summed .
Description
Cells in SUM_range are summed only if the corresponding cells in the range meet the criteria.
If SUM_range is omitted, the cells in the range are summed.
WPSTable also provides other functions that analyze data based on conditions. For example, if you want to count the number of occurrences of a text string or number within a range of cells, you can use the COUNTIF function. If you want the formula to return one of two values based on a certain condition (for example, return a sales bonus based on a specified ), you can use the IF function.
The three summation functions commonly used in work are the SUM function, the SUMIF function and the SUFIFS function.
This lesson will explain how to use the SUMIF function to perform single-condition summation.
Basic usage of SUMIF function
▪Taking this table as an example, if we want to calculate the total score of the fifth class students in this transcript.
Click the "Formula" tab-"Insert Function" button and find the SUMIF function.
Because what we want to calculate is the grades that satisfy the students in the fifth class in the class column.
So enter the B2:B10 class area in "Area";
Enter "Class Five" in "Conditions";
Enter C2:C10 in the "Summing Area", which is the score area that needs to be summed.
Click the "OK" button to get the total score of the five classes.
Reasons for getting 0 or calculation errors
When using SUMIF, the calculation is 0. This is because no data satisfying the condition is found in the cell area used for conditional judgment.
▪If we want to calculate the total scores of Class 7.
Because the specified Class 7 condition was not found in the class area of the transcript, the total score is 0.
▪Similarly, if the summation area contains content that cannot be calculated, such as text format content, it will also lead to calculation errors.
In this case, you only need to convert the cell content into a number that can be calculated.
SUMIF function sums data outside the specified conditions
▪Taking this table as an example, if we want to calculate the total scores of other classes except the first grade (5) class.
Enter the class area in "Area";
Enter "<>Class 5" in "Conditions", which is not equal to the class of Grade 1 (5);
Enter C2:C15 in the "Summing Area", which is the score area that needs to be summed.
Click the "OK" button to calculate the total scores of other classes except Grade 1 (5).
SUMIF function single field multi-condition summation
▪Taking this sales table as an example, if we want to calculate the total number of apples and bananas purchased in the first month, how should we calculate it?
Enter the formula =SUM(SUMIF(B3:B9,{"Apple","Banana"},C3:C9)) in the formula edit box
SUMIF(B3:B9,{"Apple","Banana"},C3:C9) can be seen as using the SUMIF function to calculate each in the "Fruit" areaThe number of purchases in January that meet the conditions of "apple" and "banana".
Then use the SUM function to add the purchase values obtained by SUMIF together, which is to calculate the total purchase number of apples and bananas in the first month.
SUMIF function to sum every other column
▪Taking this sales table as an example, if we want to calculate the total number of purchases in the first, second, and third months, how should we sum up every other column?
Enter the C2:H2 title line area in "Area" and use Shift+F4 to add an absolute reference.
Adding absolute references is to prevent misalignment during formula reference calculations. Regarding the use of absolute references, you can enter the keyword "absolute reference" in the WPS Academy to view and learn about it. The content will not be discussed here.
Enter "Purchase" in "Conditions";
Enter C3:H3 in the "Summing Area", which is the row area that needs to be summed;
Click the "OK" button to calculate the total purchase quantity for the first, second and third months.
Finally, use the fill handle and quickly pull down the fill formula to get the total purchase quantity of all products.
The meaning of this formula is to use the SUMIF function to calculate the area of cells that satisfy "restocking" in the C2:H2 header row.
支付宝扫一扫 
评论列表（196条）
测试