Excel function learning: Let's talk about multiple summation functions

In the previous article "Excel function learning: three multi-conditional logic functions AND(), OR(), IF()", we learned three multi-conditional logic functions AND() , OR(), IF(). Today we are going to talk about the summation function. Excel provides us with many summation functions. Is there more than just the SUM function? Today, let us learn about the summation function in Excel one by one!

NO.1 Ordinary world: SUM

As an orthodox bloodline that seeks summation, the SUM function is The oldest summation function. This function, which is directly named after the English word for sum, will be familiar to many little petals. Click the button, or enter =SUM (summation area) , or press <alt></alt> to call the SUM function to sum the data source. When array operations are not used, the operating principle of the SUM function is relatively simple, but it should still be noted that text and logical values ​​will be treated as 0 by the SUM function, and when there are error values ​​in the summing area, the SUM function will also report an error. . The most obvious characteristic of the SUM function is that it does not tolerate sand in its eyes.

Figure 1 Quickly call the SUM function in batches: Alt =

Usage and instructions:

## is the shortcut key for automatic summation. After locating the null value, calling automatic summation can automatically fill in the SUM function for the empty cells. The summation area can also be intelligently identified as adjacent contiguous ones on the left and above. Cell range. For example, in cell B5, the adjacent consecutive cells above it are B2:B4, then the formula of B5 is "=SUM(B2:B4)", that is, the sum of B2, B3, and B4.

Figure 2 Alt =

NO.2 Single condition sum: SUMIF

as The SUMIF function, with a background in summation functions, can be said to have brought revolutionary changes to the summation function family. Starting from SUMIF, seeking harmony is no longer a matter of "harmony", but truly seeks common ground while reserving differences, and achieves harmony without difference.

=SUMIF (condition area, condition, summation area) Such a function statement must be familiar to everyone in the petals. Today, let us use the SUMIF function to demonstrate how to solve the problem of summing alternate columns.

Figure 3 Single condition summation of alternate columns

Formula description:

Take N3 as an example, the SUMIF function will condition Each cell in the range B2:M2 is compared with the condition value cell N2. If they are equal, the corresponding cells in B3:M3 are summed, because B2, E2, H2 and K2 are all related to N2 They are both "sales", so the corresponding B3, E3, H3 and K3 are all added up. The $ symbol in the example represents a sign of locking the rows and columns. This operation is to be able to drag and fill the formula into the N2:P8 area.

NO.3 Multi-condition sum: SUMIFS

The SUMIFS function is an enhanced version of the SUMIF function and has almost all the features of the latter. ability. Its significant advantage is that the number of conditions it can set for the summation area is no longer limited to one, but the combination of condition areas and condition values ​​is expanded to up to 127 groups, which is a qualitative leap.

=SUMIFS(summation area, condition area 1, condition 1, condition area 2, condition 2...) is the basic syntax of the SUMIFS function. Little petals should pay attention to its relationship with the SUMIF function. The difference is that the summation area of ​​the SUMIFS function is prepended.

Figure 4 Multi-condition summation

Formula description:

Taking G2 as an example, the function of the SUMIFS function is to Compare each cell in C2:C10 with E2, compare each cell in B2:B10 with F2, and sum the corresponding summation area cells C2 and C5 that meet both conditions at the same time.

NO.4 Cross-condition summation: SUMPRODUCT

SUMPRODUCT has an unshakable dominance in the function field because it has multiple capabilities. In addition to our common multi-condition queries, product summation and cross-condition summation are also its specialties. The basic syntax of SUMPRODUCT is =SUMPRODUCT (product area 1, product area 2) . The numbers in the two product areas will be multiplied one by one and summed, that is, the sum of products; it also has a very The famous deformation syntax =SUMPRODUCT ((condition area 1 = condition 1) * (condition area 2 = condition 2)...* (summation area)) , this syntax is actually a For array operations, we will not delve into its usage in this article. We will only demonstrate it briefly using Figure 5 as an example.

Figure 5 Sum of products

Formula description:

SUMPRODUCT (A2:A10,B2:B10) means multiplying A2:A10 and B2:B10 one by one, such as A2*B2, A3*B3, etc., and finally summing these products.

Figure 6 Cross condition summation

NO.5 Visible summation: SUBTOTAL

The SUBTOTAL function also says to many small petals, like a familiar stranger, familiar but indescribable. You've definitely used it, but you probably don't recognize it. That's right, when you click while filtering, the summation function called is not SUM, but the SUBTOTAL function. =SUBTOTAL (function code, summation area 1, summation area 2...) is its basic syntax. There are many function codes in parameter 1, among which there are two related to summation, 9 and 109. When the summing area contains hidden cells, 9 means that the hidden value summation is included, and 109 ignores the hidden value summation, that is, the visible summation.

Figure 7 Visible summation

Formula description:

The function code of SUBTOTAL in C12 is 9, including hidden units Cell summation, the summing result will not change with the hidden row operation; and the function code in D12 is 109, which does not include hidden cells. Therefore, when the row where the summing area is located is hidden, the hidden cells will not be included. within the summation range.

NO.6 Ignore the sum: AGGREGATE

AGGREGATE, as the "guardian of the water fountain" in the summation function world, has always been unknown, understand Or know that its petals are few and far between. The fate of almost sitting on the bench does not match its almighty combat effectiveness. The AGGREGATE function, which has little talent, needs a chance to shine. So, even Xiaohua couldn't bear to bury it again. =AGGREGATE (9, ignore type code, summation area) is the basic statement when using AGGREGATE for summation, where 9 is the function code indicating summation in the first parameter of AGGREGATE. There are 8 ignore types, which represent ignoring different types of data. The details are as follows:

Figure 8 Ignore type code table

We might as well use AGGREGATE to complete None of the above functions can complete summing ignoring error values.

Figure 9 Ignore error summation

Formula description

When the SUM function is summed, the summation area cannot be calculated There are errors in the summation formula; AGGREGATE can overcome this defect and ignore the error values ​​in the summation formula.

NO.7 Database summation: DSUM

As a member of the database function, the DSUM function is inevitably unknown, full of unknown and mysterious colors . Today Xiaohua takes you to unveil its mystery. The function of DSUM is to return the sum of the field column numbers of the records in the list or database that meet the conditions. =DSUM (list area or database, field, condition area) is its basic statement, where the condition area is composed of field label cells and cells representing conditions.

Figure 10 Database summation

Formula description

The summation list area is A1:D10. This area must contain the summation column and the condition column, and its first row must be the field title (product name, 2L, etc.); B1 in the formula indicates that the summation field name is "2L ”, the field value is one of the title row labels of the list area; and the first line of the condition area F1:G2 is the field label, which must be included in the list area, and its second line is the formula value, represented by F1:F2 The first condition is that the product name contains "C product", and G1:G2 means that the second condition is that the output of 2L is greater than 0. Use the DSUM function to sum the values ​​in column B that meet the two conditions.

The application examples of the summation function introduced in this article are relatively simple. The main purpose is to make the little petals familiar with them so that they can choose the best ones according to the situation. These functions seem simple, but if you study in depth, you will find that the deformation of the function, the use of wildcards, array summation, etc. can greatly improve the functionality of the summation function.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel function learning: Let's talk about multiple summation functions. For more information, please follow other related articles on the PHP Chinese website!