使用Excel COUNTIF函数对大于设置数量的单元格进行计数

One of the tasks you have to do quite often in Excel is to count things. Here's how to count cells greater than set amount with Excel COUNTIF function.

在Excel中必须经常执行的任务之一是计算事物。 这是使用Excel COUNTIF函数计算大于设置数量的单元格的方法。

学生考试成绩 (Student Test Scores)

In this example, we have a list of students and their test scores.

在此示例中，我们列出了学生及其考试成绩。

If the passing score is 50, how can you get Excel to count the number of students who passed the test?

如果通过分数是50，您如何获得Excel来计算通过考试的学生人数？

使用COUNTIF和运算符 ( Use COUNTIF and an Operator)

The Excel COUNTIF function will count cells, based on the criteria that you enter. As part of the criteria, you can use an operator, such as greater than, or less than, to count a specific range of numbers.

Excel COUNTIF函数将根据您输入的条件对单元格进行计数。 作为条件的一部分，可以使用大于或小于的运算符来计算特定范围的数字。

In this example, the passing score is 50. To find the students who failed the test, you'd count the scores that are less than 50 -- <50.

在此示例中，及格分数为50。要查找未通过测试的学生，您可以计算分数小于50-<50。

To include this operator in the COUNTIF criteria, enclose the operator and number in double quote marks.

要将此运算符包括在COUNTIF标准中，请将该运算符和数字括在双引号中。

=COUNTIF(B2:B11,"<50")

= COUNTIF(B2：B11，“ <50”)

将单元格引用与COUNTIF条件一起使用 (Use a Cell Reference with COUNTIF Criteria)

Instead of typing a number in the COUNTIF criteria, you can use a cell reference. In this example, cell F5 contains the passing score.

您可以使用单元格引用来代替在COUNTIF条件中键入数字。 在此示例中，单元格F5包含通过分数。

To find the students who passed the test, you'd count the scores that are greater than or equal to the value in cell F5 -- >=F5.

要找到通过考试的学生，您可以计算分数，该分数大于或等于单元格F5中的值-> = F5。

To include a cell reference and operator in the COUNTIF criteria, enclose the operator in double quote marks, and use an ampersand before the cell reference.

若要将单元格引用和运算符包括在COUNTIF标准中，请在双引号中包含运算符，并在单元格引用之前使用＆符。

=COUNTIF(B2:B11,">=" & F5)

= COUNTIF(B2：B11，“> =”＆F5)

单元格引用自动更新 (Cell Reference Automatically Updates)

An advantage to using a cell reference in the COUNTIF criteria is that the result will automatically update, if the value in the referenced cell changes.

在COUNTIF标准中使用单元格引用的一个优点是，如果引用的单元格中的值发生更改，结果将自动更新。

In the screenshot below, you can see that the passing score has been increased from 50 to 70. Because the Passed formula uses a reference to cell F5, the count is automatically updated.

在下面的屏幕截图中，您可以看到合格分数已从50增加到70。由于“合格”公式使用对单元格F5的引用，因此该计数会自动更新。

The Failed formula has the passing score of 50 typed into the criteria, so that value would have to be changed, before the result is updated.

失败公式的合格分数为50，并输入标准，因此在更新结果之前必须更改值。

观看COUNTIF视频 (Watch the COUNTIF Video)

To see the steps for creating a COUNTIF formula with an operator, you can watch this short Excel tutorial video.

要查看使用运算符创建COUNTIF公式的步骤，您可以观看这段简短的Excel教程视频。

演示地址

翻译自: https://contexturesblog.com/archives/2010/06/28/count-cells-greater-than-set-amount-with-excel-countif-function/