SUMIF函数的目的是对满足您指定条件的范围内的值求和,或基于单个条件添加范围。SUMIF接受三个参数Range、Criteria和Sum-range。参数是函数用于在公式中执行运算或计算的值。
SUMIFS函数添加满足多个条件的所有参数。SUMIFS函数的顺序与SUMIF函数不同。在SUMIF中,Sum_ 范围(Sum_ range)是第三个参数。在SUMIFS中,Sum_range是第一个参数。
SUMIF和SUMIFS 语法(SUMIFS Syntax)之间的区别
SUMIF 语法(SUMIF Syntax)
- Range:您希望函数计算的单元格范围。范围内的单元格必须是数字、名称、数组和包含数字的引用。
- Criteria:您必须在第一个参数中提供的范围内查找的条件或标准。条件采用文本、数字和表达式的形式,用于标识将添加哪些单元格。
- Sum_range:要求和的范围或要添加的单元格。Sum_range是(Sum_range)可选的。
公式是SUMIF (range, criteria, [sum_range])
。
SUMIFS 语法(SUMIFS Syntax)
- Sum_range:要添加的单元格范围。
- Criteria_range 1:使用标准 1 分析的单元格范围。
- 标准 1(Criteria 1):确定要添加的单元格范围。
- Criteria_range2, criteria2:附加范围及其相关标准。此参数是可选的。公式是
SUMIFS (sum_range, criteria_range1, criteria1 [criteria_range2, criteria2,..]
。
如何使用 Excel SUMIF 函数
在此表中,我们要计算出售的芒果数量。
我们将单击要放置结果的单元格。
然后在单元格中输入=SUMIF (
我们将寻找 Range。在本文中, Range 是您看到水果的地方,因为我们想要找到售出的芒果数量。
移动光标并单击水果列中的单元格 B3,然后按 Ctrl、 Shift和 向下箭头键(Down Arrow Key)将列中的数据突出显示为B10或键入B3:B10。您将在公式中看到单元格范围(Range)。然后加一个逗号。
然后添加Criteria,也就是Mango,因为我们要查找芒果的总和,然后添加一个逗号。
添加Sum_range,该数据位于标记为Number of Sales的列中,显示每个客户购买的商品。
单击(Click)Number of Sale列中第一个数据的单元格。按Ctrl、 Shift和向下箭头键(Down Arrow Key)突出显示列的数据。然后添加一个括号或输入C3:C10。
按(Press)回车。你会看到结果。
另一个选项是转到函数和库组中的公式选项卡;(Formulas)点击数学和三角(Math and Trig)。
在下拉菜单中,选择SUMIF。将出现一个函数参数对话框。
在范围(Range)输入框中,键入B3:B10
.
在Criteria输入框中,键入Mango ,因为我们想知道购买了多少芒果。
在Sum_range输入框类型中C5:C10
,此列是购买或定位的项目数。
按确定(OK)。你会看到结果。
如何在Excel中使用(Excel)SUMIFS函数
在本教程中,我们要添加我们从Kevin Sahadeo制作的芒果的总销量。
单击(Click)您希望结果所在的单元格。在单元格中键入(Type), =SUMIFS ()
。
在SUMIFS函数中,我们首先添加Sum_range,即Number of Sales列中的数据,指定每个客户购买了多少商品。
键入单元格C3:C10或单击 C3 并按Ctrl、Shift和向下箭头键(Down Arrow Key)以突出显示C10,然后添加逗号。
添加条件范围 1(Criteria range 1)。单击水果列中的第一个数据并输入单元格B3:B10或Ctrl、Shift和向下箭头键(Down arrow key)以突出显示单元格。
键入 Mango 作为Criteria。
现在我要添加Second Criteria Range。转到Customer列并单击表中的第一个数据,然后单击Ctrl、Shift和向下箭头键(Down Arrow Key)以突出显示或键入A3:A10,即逗号。
我们将添加第二个标准(Second Criteria),即Kevin Sahadeo。确保在公式中键入准确的拼写并使用全名。按Enter,您将看到结果。
另一个选项是单击函数库组中的公式(Formulas)选项卡,在下拉菜单中选择数学和三角函数,选择(Math and Trig)SUMIFS。将出现一个函数参数对话框。
在Sum_range输入框中,键入C3:C10
因为这是我们要加起来的销售额。
在Criteria_ range输入框中键入B3:B10
,因为这是我们正在寻找的水果所在的范围。
在Criteria输入框中,键入因为我们想知道Kevin Sahadeo购买Mango
了多少芒果。
在Criteria_ Range 2输入框中键入A3:A10
,因为这是您要查找的第二个范围,名称为Kevin Sahadeo。
在Criteria 2输入框中,键入Kevin Sahadeo
因为我们想了解此人购买了多少芒果。
然后单击确定。你会看到结果。
我希望你觉得这篇文章有用。
阅读下一篇(Read next):如何在 Excel 中创建数据透视表和数据透视图(How to create a Pivot Table and Pivot Chart in Excel)。
How to use SUMIF and SUMIFS Functions in Excel
The SUMIF function purpose is to sum the values in a range that meets the criteria you specify or add a range based on a single criterion. SUMIF takes three arguments Range, Criteria, and Sum-range. Arguments are values that a function uses to perform operations or calculations in a formula.
The SUMIFS function adds all its arguments that meet multiple criteria. The orders of the SUMIFS function is different from the SUMIF function. In SUMIF, the Sum_ range is the third argument. In SUMIFS, the Sum_range is the first argument.
Difference between SUMIF and SUMIFS Syntax
SUMIF Syntax
- Range: The range of cells you want the function to calculate. The cells in range must be numbers, names, arrays, and references that contain numbers.
- Criteria: The condition or criteria you must look up for in the range provided in the first argument. The criteria are in the form of a text, number, and expression that identifies, which cells will be added.
- Sum_range: The range to be sum or cells to be added. The Sum_range is optional.
The formula is SUMIF (range, criteria, [sum_range])
.
SUMIFS Syntax
- Sum_range: the range of cells to add.
- Criteria_range 1: The range of cells that is analyzed using criteria 1.
- Criteria 1: Determines the range of cells to add.
- Criteria_range2, criteria2: The additional ranges and their associated criteria. This argument is optional. The formula is
SUMIFS (sum_range, criteria_range1, criteria1 [criteria_range2, criteria2,..]
.
How to use the Excel SUMIF function
In this table, we want to calculate the number of mangoes sold.
We are going to click the cell where we want to place the result.
Then type in the cell =SUMIF (
We are going to look for the Range. In this article, the Range is where you see the fruits because we want to find the number of mangoes sold.
Take the cursor and click on the cell B3 in the fruit column, then press the Ctrl, Shift, and the Down Arrow Key to highlight the data to B10 in the column or type B3:B10. You will see the Range of cells in the formula. Then add a comma.
Then add the Criteria, which is Mango, because we want to find the sum of mangoes sold, then add a comma.
Add the Sum_range, the data located in the column labeled Number of Sales, that Displays the items purchased by each customer.
Click the cell of the first data in the Number of Sale columns. Press the Ctrl, Shift, and the Down Arrow Key to highlight the column’s data. Then add a bracket or type C3:C10.
Press enter. You will see the result.
The other option is to go to the Formulas tab in the function and library group; click Math and Trig.
In the drop-down menu, select SUMIF. A function argument dialog box will appear.
In the Range entry box, type the B3:B10
.
In the Criteria entry box, type Mango because we want to find how many mangoes were Purchased.
In Sum_range entry box type C5:C10
, this column is where the number of items purchased or located.
Press OK. You will see the result.
How to use the SUMIFS function in Excel
In this tutorial, we want to add the total number of sales of mangoes we made from, say, Kevin Sahadeo.
Click the cell where you want the result to be. Type, =SUMIFS ()
in the cell.
In the SUMIFS function, we will add the Sum_range first, which is the data in the Number of Sales columns, which specify how many items were purchased by each customer.
Type the cell C3:C10 or click on C3 and press the Ctrl, Shift, and Down Arrow Key to highlight down to C10, then add a comma.
Add the Criteria range 1. Click the first data in the fruits column and enter the cells B3:B10 or Ctrl, Shift and Down arrow key to highlight the cells.
Type Mango as Criteria.
Now I am going to add the Second Criteria Range. Go to the Customer column and click on the first data in the table and Ctrl, Shift, and Down Arrow Key to highlight or type A3:A10, the comma.
We are going to add the Second Criteria, which is Kevin Sahadeo. Ensure you type it the exact spelling in the formula and use the full name. Press Enter you will see the result.
The other option is to click on the Formulas tab in the function library group, select Math and Trig in the drop-down menu, select SUMIFS. A function argument dialog box will appear.
In the Sum_range entry box, type C3:C10
because this is the number of sales we want to add up.
In the Criteria_ range entry box type B3:B10
because this is the range where the fruit, we are looking for is.
In the Criteria entry box, type Mango
because we want to find how many mangoes Kevin Sahadeo purchased.
In the Criteria_ Range 2 entry box type A3:A10
, because this is the second range you are looking up, where the name Kevin Sahadeo is.
In the Criteria 2 entry box, type Kevin Sahadeo
because we want to find out how many mangoes this person purchases.
Then click OK. You will see the result.
I hope you find the post useful.
Read next: How to create a Pivot Table and Pivot Chart in Excel.