In Excel, the sumifs function is a very practical function that can sum data that meets multiple conditions. To use the sumifs function, you need to fill in three conditions, namely: sum range, condition area, and condition.
1. The syntax of the sumifs function
sumifs(sum range, condition area 1, condition 1....
2. Examples of use
How do I fill in these three conditions?Let's illustrate with an example.
Let's say we have a sales data table with information such as sales, product categories, and sales territories. Now we want to calculate the sales in North China, where the sales are in column A, the product category is in column B, and the sales region is in column C.
requirements, what is the sales volume that meets the following three conditions?
1.Sales greater than 0;
2.The product category is "Mobile Phones";
3.The sales area is "North China".
First, let's set the first conditional range to column a, which is the sales column. Then, set the first condition to ">0", which means that only rows with sales greater than 0 are counted.
Next, we set the second conditional range to column b, which is the product category column. Set the second condition to "Phone", which means that only rows with the product category "Phone" are counted.
Finally, we set the third conditional range to column C, which is the sales territory column. Set the third condition to North China, which means that only rows with a sales territory of North China are counted.
With the above settings, we get the three conditions of the sumifs function, and the final result is the sum of sales that meet these conditions.
The full expression formula is as follows:
=sumifs(a2:a9,a2:a9,">0",b2:b9,"Mobile phones",c2:c9,"North China")
It should be noted that when setting the condition here, you need to put the condition in half-width symbol quotation marks (that is, switch the input method to quotation marks in the English state), otherwise the formula will report an error and cannot be executed.
Of course, the use of sumifs function is not limited to this scenario, it can achieve a variety of complex data processing and analysis by setting different condition ranges and condition combinations. Proficiency in using sumifs functions will greatly improve data processing capabilities and work efficiency.