How to use the SUMIF function in Excel

Source: Internet
Author: User

How to use SUMIF functions in Excel

The use of the SUMIF function in Excel is to sum several cells, ranges, or references based on a specified condition.

Parameters of the SUMIF function: Range,criteria,sum_range

1 range is a conditional range for the range of cells in which the criteria are judged.

2 The criteria is the summation condition, by the numeral, the logical expression and so on constituent's judgment condition. A condition that determines which cells will be added together in the form of numbers, expressions, or text. For example, a condition can be expressed as 32, "32", ">32", or "apples". You can also use wildcard characters, such as "? 2*", to simplify the formula setting if you want to sum a condition that has a second number of 2.

3) Sum_range is the actual sum region, the cell, range, or reference that needs to be summed.

When the third argument is omitted, the conditional range is the actual summation area.

Wildcard characters are used in the criteria argument, including question marks (?) and asterisks (*). The question mark matches any single character, and the asterisk matches any string of characters. If you want to find the actual question mark or asterisk, type the tilde (~) before the character.

The cells in Sum_range are summed only if the corresponding cells in the range match the criteria.

If Sum_range is omitted, the cells in the range are summed.

Microsoft Excel also provides a number of other functions that can analyze data based on criteria. 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 a value of two values based on a condition (for example, to return a sales dividend based on a specified amount of sales), you can use the IF function.

Two: an example of Excel SUMIF function

Example: To find the total flow of each column in the report

The results are shown in the following figure.

Select the F2 cell, enter the formula: =SUMIF (B2:B19,E2,C2:C19), after the formula is complete, press the keyboard Ctrl+shift+enter key combination (you must press this Ctrl+shift+enter key array formula to get the correct result), You can count the total flow of Office software column.

And so on, select F3 cell, input formula: =SUMIF (B2:B19,E3,C2:C19), input formula completed, press the keyboard Ctrl+shift+enter key combination, you can get the total flow of the website Operation column.

Select F4 cell, input formula: =SUMIF (B2:B19,E4,C2:C19), input formula completed, press the keyboard Ctrl+shift+enter key combination, you can get the total flow of computer Hospital column.

Select F5 cell, input formula: =SUMIF (B2:B19,E5,C2:C19), input formula completed, press the keyboard Ctrl+shift+enter key combination, you can obtain the total flow of tool software column.

Select the F6 cell, enter the formula: =SUMIF (B2:B19,E6,C2:C19), the input formula is completed, press the keyboard Ctrl+shift+enter key combination, you can obtain.

Related Keywords:
Related Article E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD