The SUM function in Excel allows for quick summation, while the SUMIFS function enables the “filtering” of values based on specified criteria before summation. These functions are essential in various fields such as marketing, finance, sales, and administration. When combined with features like Excel drop-down menus, they can fulfill a wide range of needs.
重點整理
SUM Function
To display the sum result, simply input “=SUM(range to be summed)” in the desired location. Alternatively, you can hold down Ctrl and click on each number to be summed individually. However, it is more common to specify a range for summation. For example, in the image below, we sum the Qty values of three items (54+15+12).
P.S. Qty stands for Quantity.
You can also go to the toolbar, click on “Formulas,” then choose “Insert Function,” and select SUM. In the dialog box, select the range to be summed.
SUMIF Function
SUMIF is used to sum data based on specified criteria. Its basic syntax is as follows:
SUMIF(range, criteria, [sum_range])
- range: The range to be checked, i.e., the range where data will be filtered and summarized (blue range in the image).
- criteria: The criteria used for filtering, which can be a value, text, or expression (green “Water” bottle in the image).
- sum_range: The range to be summed based on the filtering criteria (purple Qty values in the image). If omitted, the SUMIF function directly sums the range.
Note: need to fix range, criteria, sum_range?
In the example above, when performing the SUMIF calculation for bottled water and wanting to use the drag-and-drop feature to copy the formula for soda, the entire range, criteria, and sum_range will shift down one cell. The criteria part may work fine, but the first Qty value for soda (28) will be omitted. To fix this, press F4 to anchor the references, as shown in the image. Sometimes, you may also need to anchor the criteria part. Click here for more information.
SUM + IF Application:
This formula uses the IF function to check if each score is greater than or equal to 60. If true, the score is included in the calculation; otherwise, it is treated as 0. Finally, the SUM function adds up these values and returns the total sum of passing students.
=SUM(IF(A2:A5>=60, A2:A5, 0))
The SUM function has a wide range of applications, whether it’s calculating financial data, generating statistical reports, or processing student grades. By mastering this powerful function, you’ll be able to handle data more efficiently and save a significant amount of time and effort.
SUMIFS Function:
SUMIFS is used for summation after applying multiple criteria. Sometimes, we need to filter and sum data based on multiple conditions. For example, if we want to calculate the total sales amount of Product A in January 2023, we can use the following formula:
=SUMIFS(C2:C10, A2:A10, "Product A", B2:B10, "January 2023")
This formula simultaneously checks the ranges A2 to A10 and B2 to B10. Only when the product name is “Product A” and the sales month is “January 2023” will it sum the values in the range C2 to C10.
Click here for more excel guides