bzdww

Get answers and suggestions for various questions from here

Excel function formula: conditional summation: SUMIF function! ! !

cms

In office learning, the data is subject to conditional summation, and the SUMIF function is one of the functions with the highest frequency of conditional summation.


First, the basic syntax analysis of the SUMIF function.

The SUMIF function can conditionally sum the eligible numbers within the specified range, and the conditional summation function is very powerful.

Grammatical structure: SUMIF(range,ceiteria,[sum_range]).

Range : Required. The range of cells used for conditional calculations.

Criteria : Required. A condition used to determine a summation cell, which can be in the form of a number, an expression, a cell reference, a text, or a function.

Sum_range : optional. The actual cell of the request and if you want to sum the cells that are not specified in the range parameter. If the sum_range parameter is omitted, Excel will sum the cells specified in the range parameter (that is, the cells that apply the condition).

Example:

Purpose: To find the sum of female students' grades.

Method: Enter the formula in the target cell: =SUMIF(C3:D7, "female", D3:D7).


Second, the sum of the SUMIF function statistics single field and single conditions.

method:

Enter the formula in the target cell: =SUMIF(D3:D7,">89").


Third, the SUMIF function statistics single field, multi-condition sum.

method:

Enter the formula in the target cell: =SUM(SUMIF(B3:B7,{"Li Li","Li Zhang", "Ming Ming"}, D3:D7)).

Interpretation:

If calculated according to conventional thinking, Li Li's score is calculated as: =SUMIF (B3:B7, "Li Li", D3:D7), then Li Zhang, the clear scores can be added. But it is very troublesome. So, we can write the sum of the three formulas as: =SUM(SUMIF(B3:B7,{"Li Li", "Li Zhang", "Ming Ming"}, D3:D7)).


Fourth, the SUMIF function counts the sum of the top three results.

Purpose: To calculate the sum of the top three results.

method:

Enter the formula in the target cell: =SUMIF(D3:D7,">"&LARGE(D3:D7,4)).

Interpretation: The function LARGE (RANGE, K) is to return the K maximum values ​​in the array. If LARGE(D3:D7,4) returns a value of 90, it is not difficult to understand the formula: =SUMIF(D3:D7,">"&LARGE(D3:D7,4)).


Five, SUMIF function fuzzy conditional summation.

Purpose: To calculate the sum of the results of the "Li" surname.

method:

Enter the formula in the target cell: =SUMIF(B3:B7, "Li*", D3:D7).


Sixth, SUMIF function statistics registrant non-empty warehousing.

method:

Enter the formula in the target cell: =SUMIF(B3:B7,"*",D3:D7).


Seven, SUMIF function is divided into categories and statistics.

method:

1. Select the target cell.

2. Enter the formula in the target cell: =SUMIF($D$3:$K$3,L$3,$D4:$K4).

3. Ctrl+Enter is filled.


Eight, SUMIF function to achieve the search function.

method:

1. Select the target cell.

2. Enter the formula in the target cell: =SUMIF($B$3:$B$7,$I3,D$3:G$3)

3. Ctrl+Enter is filled.