Excel中执行简单数学计算的三个最常用的公式是COUNT、SUM和AVERAGE。无论您是在Excel中管理财务预算还是只是跟踪您的下一次假期，您之前可能已经使用过这些功能之一。
在本文中，我们将介绍这三个函数及其相关且有用的对应函数的基础知识：COUNTIFS、SUMIFS 和 AVERAGEIFS(SUMIFS and AVERAGEIFS)。
Excel 计数、总和和平均值
要知道我们卖出了多少部手机，我们可以快速使用如下所示的COUNT公式：(COUNT)
=COUNT(E2:E16)
另一方面，要获得我们所做的总销售额，我们可以使用SUM公式，如下所示：
=SUM(E2:E16)
最后，要找出我们为所有手机所做的平均销售额，我们可以使用以下平均(AVERAGE)公式：
=AVERAGE(E2:E16)
结果应如下所示：
COUNT、SUM 和 AVERAGE 公式(SUM and AVERAGE formulas)仅适用于单元格值为(cell value)数字格式(number format)的记录。公式范围(formula range)内的任何记录（即本例中的E2:E16）不是数字格式(number format)的记录都将被忽略。
因此，请确保COUNT、SUM 和 AVERAGE 公式(SUM and AVERAGE formula)中的所有单元格都格式化为Number，而不是Text。尝试使用相同的公式，但使用E:E而不是E2:E16作为范围。它将返回与以前相同的结果，因为它忽略了文本格式的标题（即销售价格）。(Sale Price)
现在，如果我们想知道仅在美国(USA)销售的产品的销售数量、销售总额和每部手机的平均销售量怎么办？这就是COUNTIFS、SUMIFS 和 AVERAGEIFS(SUMIFS and AVERAGEIFS)发挥重要作用的地方。观察下面的公式：
计数(COUNTIFS)
公式分解：
- =COUNTIFS( – “=”表示单元格中公式的开头，而COUNTIFS是我们正在使用的Excel 函数(Excel function)的第一部分。
- D2:D16 –指要(Refers)检查的数据范围，以查看它是否满足要包含在计数公式(count formula)中的标准。
- “USA” –在指定数据范围内(data range)查找的标准(Criteria)( D2:D16 )
- ) –右括号(Closing bracket)表示公式的结束。
该公式返回 6，这是从美国仓库(USA warehouse)发货的产品的销售数量。
SUMIFS
公式分解：
- =SUMIFS( – “=”再次表示公式的开头。
- E2:E16 –指(Refers)我们想要汇总的数据范围，即我们示例中的销售价格(sale price)。
- D2:D16 –指要(Refers)检查的数据范围是否满足包含在总量中的标准。
- “USA” –在指定数据范围内(data range)查找的标准(Criteria)( D2:D16 )
- ) –右括号(Closing bracket)表示公式的结束。
该公式显示从美国仓库(USA warehouse)发货的产品的总销售额$6,050。
平均值(AVERAGEIFS)
公式分解：
- =AVERAGEIFS( – “=”表示公式的开头。
- E2:E16 –指(Refers)我们想要平均的数据范围。在此示例中，我们希望获取在美国(USA)销售的所有手机的平均销售额。
- D2:D16 –指要(Refers)检查的数据范围是否满足要包含在平均公式中的标准。
- “USA” ——在指定数据范围内(data range)查找的标准(Criteria)
- ) –右括号(Closing bracket)指示公式的结尾。
该公式显示，我们在美国以每部手机约$1,008美元的价格出售了该产品。
所有三个公式都可以采用多个标准。例如，如果我们想知道在美国销售的产品的相同数字（即(USA)COUNT、SUM和AVERAGE），但仅针对 三星(Samsung)品牌，我们只需添加要检查的数据范围及其标准。
请(Please)参阅下面的示例，其中将第二个标准添加到初始标准检查中。（蓝色文字(Blue text)表示第一个标准，红色表示第二个标准）
=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")
=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")
=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")
您会注意到Excel也有 没有后缀“S”的(“S”)COUNTIF、SUMIF和AVERAGEIF公式。它们的使用类似于COUNTIFS、SUMIFS和AVERAGEIFS。但是，公式中没有后缀“S”的那些具有每个公式只允许一个标准的限制。
由于语法略有不同，我建议仅使用 COUNTIFS、SUMIFS和AVERAGEIFS，因为它可以用于一个或多个条件（如有必要）。享受！
How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel
The three most commonly used formulas in Excel that perform simple mathematical calculatiоns arе COUNT, SUM and AVERAGE. Whether you are managing a financial budget in Excel or simply keeping track of your next vacation, you’ve probably used one of these functions before.
In this article, we’re going to go through the basics of these three functions and their relevant and useful counterparts: COUNTIFS, SUMIFS and AVERAGEIFS.
Excel COUNT, SUM and AVERAGE
To know how many mobile phones that we have sold, we can quickly use the COUNT formula as shown below:
=COUNT(E2:E16)
On the other hand, to get the total amount of sales that we have made, we can use the SUM formula as shown below:
=SUM(E2:E16)
Lastly, to find out the average sales that we made for all phones, we can use the AVERAGE formula as below:
=AVERAGE(E2:E16)
The result should be as below:
COUNT, SUM and AVERAGE formulas will only work for records where the cell value is in number format. Any record within the formula range (i.e. E2:E16 in this example) not in the number format will be ignored.
So, please ensure that all cells within the COUNT, SUM and AVERAGE formula are all formatted as Number, not Text. Try to use the same formula, but with E:E as the range instead of E2:E16. It will return the same result as before because it ignores the header (i.e. Sale Price), which is in text format.
Now, what if we want to know number of sales, total amount of sales and the average amount of sales per phone, just for those sold in USA? This is where COUNTIFS, SUMIFS and AVERAGEIFS play an important role. Observe the formula below:
COUNTIFS
Formula breakdown:
- =COUNTIFS( – The “=” indicates the beginning of a formula in the cell and COUNTIFS is the first part of the Excel function that we are using.
- D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the count formula.
- “USA” – Criteria to look for in the data range specified (D2:D16)
- ) – Closing bracket indicating the end of the formula.
The formula returns 6 which is the number of sales for products shipped from the USA warehouse.
SUMIFS
Formula breakdown:
- =SUMIFS( – The “=” indicates the beginning of the formula again.
- E2:E16 – Refers to range of data that we would like to total, i.e. sale price in our example.
- D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the total amount.
- “USA” – Criteria to look for in the data range specified (D2:D16)
- ) – Closing bracket indicating the end of the formula.
The formula shows $6,050 total sales that were made for products shipped from the USA warehouse.
AVERAGEIFS
Formula breakdown:
- =AVERAGEIFS( – The “=” indicate the beginning of formula.
- E2:E16 – Refers to range of data that we would like to average. In this example, we want to get the average amount of sales for all phones sold in the USA.
- D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the average formula.
- “USA” – Criteria to look for in the data range specified
- ) – Closing bracket indicating the ends of the formula.
The formula shows we sold the product for around $1,008 per phone in USA.
All three formula can take more than one criteria. For example if we want to know the same figures (i.e. COUNT, SUM and AVERAGE) for products sold in USA, but specifically only for the Samsung brand, we just need to add the data range to be checked followed by its criteria.
Please see example below where a second criteria is added to the initial criteria checks. (Blue text indicates the first criteria and red indicates the second criteria)
=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")
=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")
=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")
You will notice that Excel also has COUNTIF, SUMIF and AVERAGEIF formulas without the suffix “S”. Those are used similar to COUNTIFS, SUMIFS and AVERAGEIFS. However, those without the suffix “S” in the formula have the limitation of only allowing one criteria per formula.
As the syntax is slightly different, I would recommend using COUNTIFS, SUMIFS and AVERAGEIFS only as it can be used for either one criteria or more, if necessary. Enjoy!
