What are the ways to use Excel table Sumifs

excelsumifs use is as follows:

One, sumif function of a strange use

The SUMIF function in an Excel table is used frequently in conditional summation, and is generally used to calculate the sum of numbers in another column based on a column.

Example 1: False set A is the Department, column B is the name, c column is the wages, you can use the following formula to calculate the total wages of all Finance department employees:

=sum (a1:a100, "Finance Department", c1:c100)

Today is not the formal use of this, but to introduce a seemingly unrelated to sumif examples.

The following figure, is some irregular data, only focus on the area of the fill color, now to calculate all the sum of the following numbers, it seems that this problem does not have a good idea, in fact, this problem can be easily solved with the SUMIF function.

=SUMIF (B2:e7,b12,b3:e8)

To analyze this formula, the second parameter is a condition to understand, what does the first argument have to do with the second parameter? We'll see that the third parameter is exactly the area of the first argument that is offset by one unit.

To sum up, the SUMIF function does not sum only the rule regions, but also the irregular permutations.

Second, SUMIF function introductory Chapter

SUMIF, as a conditional summation function in Excel2003, plays a powerful role in practical work, although it is replaced by Sumifs after 2007, but it is still a classic of Excel function. In particular, advanced usage is still applicable to later versions.

Sum is sum, if is if. If anything. The sum, in fact, is the sum of the conditions. Its structure is as follows:

SUMIF (condition judgment area, condition, summation region)

Description

1 condition judgement Area. namely is the condition sum, one is to have the condition, the second is to have the judgment area, the SUMIF second parameter is the summation condition, the first parameter is takes the condition to this area to compare the area. The first argument must be a cell reference.

2, conditions. A conditional sum is conditional, which can be a string ("ABC"), a conditional expression (">100") that is connected with a comparison symbol greater than, or a wildcard character to represent a matching sum ("Ab*c").

3. Sum area. The SUM function, which is a range of cells, can be counted as a sum function, which is a range reference. such as c2:c100.

4. What if the sum region and the judging area are a region? If you calculate the sum of numbers greater than 100 in c2:c100. In this case the SUMIF function is transformed from three parameters to two parameters. That

SUMIF (summation region, condition)

Example: The following figure shows. Request according to the list of the merchant in the left table, generate the summary table on the right, sum up the total purchase quantity and total amount of the merchant.

Formula:

=SUMIF ($A $: $A $, $F 3,c$2:c$8)

Formula Description:

$A $: $A $ is the judge area, take F3 business name in this area to judge whether the same, if on the same, the C2:c8 area of the sum.

What if you just press C to sum it up? For example, calculate the sum of the amount of the C column amount greater than 200. The formula can be transformed to:

SUMIF (c$2:c$8, ">200")

Three, SUMIF function skill Chapter

After learning the introductory chapter of the SUMIF function, you will learn some of the techniques commonly used in SUMIF functions in common techniques.

When it comes to separating columns, you might think of an array formula that you can easily implement with the SUMIF function.

Example: Calculates the planned and actual totals in the expense table.

Formula: =SUMIF (B$2:G$2,B$2,B3:G3)

Hint: The SUMIF function is not only left and right, but also can sum up and down.

Four, the SUMIF function enters the order the article

The SUMIF function can only be summed with one condition in general, but in two cases it is possible to sum multiple conditions. What is the two case?

The second parameter of the SUMIF function is the "sum condition", which can be in an array form. Like what

{"A", "B"} {">5", "<10"}

When a condition uses an array form, SUMIF calculates the result based on two conditions, and then calculates the result with sum.

1, to sum up a number of goods

Example: The following figure shows. Requirements for the calculation of the product name for the mobile phone and refrigerator sales of the sum.

Formula: =sum (SUMIF (a1:a9,{"mobile", "fridge"},c2:c9))

Formula Description:

SUMIF (a1:a9,{"Cell phone", "fridge"},c2:c9) will calculate the handset sales (16) and the refrigerator sales (14) respectively. Returns an array, that is: {16,14},

SUM (): Adds two times to the two sales that SUMIF returns.

2. Sum by number interval

Example: As shown in Figure one. The sum of sales figures of 5, less than 10, is required to calculate the sales volume.

Formula: =sum (SUMIF (c2:c9,{"<10", "<5"}) *{1,-1})

Formula Description:

SUM (SUMIF (c2:c9,{"<10", "<5"}): Because greater than 5, less than 10 intervals and can be <10 and minus <5. So first we calculate the and {21,10} of these two intervals respectively.

SUMIF () *{1,-1} because the end result is a two-digit subtraction, so here the second number is changed to a negative {21,-10} using the array and array calculations. That way, you can subtract these two numbers by summing the sum later. That is, sum ({21,-10}) =11