Countif的用法

最新推荐文章于 2024-10-12 16:11:33 发布

转载最新推荐文章于 2024-10-12 16:11:33 发布 · 2.2k 阅读

文章标签：

#wildcard #character #reference #excel #less #string

Office 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了Excel中COUNTIF函数的使用方法，包括语法结构、参数解释及注意事项，并通过两个实例展示了如何利用COUNTIF进行条件计数，特别是如何使用通配符进行灵活匹配，以及如何处理空白单元格。

Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Remarks

You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.
- To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function.
- To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
- To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

Example 1: Common COUNTIF formulas

The example may be easier to understand if you copy it to a blank worksheet.

Show How to copy an example

1

2
3
4
5

A	B
Data	Data
apples	32
oranges	54
peaches	75
apples	86
Formula	Description (result)
=COUNTIF(A2:A5,"apples")	Number of cells with apples in the first column above (2)
=COUNTIF(A2:A5,A4)	Number of cells with peaches in the first column above (1)
=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)	Number of cells with oranges or apples in the first column above (3)
=COUNTIF(B2:B5,">55")	Number of cells with a value greater than 55 in the second column above (2)
=COUNTIF(B2:B5,"<>"&B4)	Number of cells with a value not equal to 75 in the second column above (2)
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")	Number of cells with a value greater than or equal to 32 and less than or equal to 85 in the second column above (3)

Example 2: COUNTIF formulas using wildcard characters and handling blank values

The example may be easier to understand if you copy it to a blank worksheet.

Show How to copy an example

1

2
3
4
5
6
7

A	B
Data	Data
apples	Yes

oranges	NO
peaches	No

apples	YeS
Formula	Description (result)
=COUNTIF(A2:A7,"*es")	Number of cells ending with the letters "es" in the first column above (4)
=COUNTIF(A2:A7,"?????es")	Number of cells ending with the letters "les" and having exactly 7 letters in the first column above (2)
=COUNTIF(A2:A7,"*")	Number of cells containing text in the first column above (4)
=COUNTIF(A2:A7,"<>"&"*")	Number of cells not containing text in the first column above (2)
=COUNTIF(B2:B7,"No") / ROWS(B2:B7)	The average number of No votes including blank cells in the second column above formatted as a percentage with no decimal places (33%)
=COUNTIF(B2:B7,"Yes") / (ROWS(B2:B7) -COUNTIF(B2:B7, "<>"&"*"))	The average number of Yes votes excluding blank cells in the second column above formatted as a percentage with no decimal places (50%)

NOTE To view the number as a percentage, select the cell and click Cells on theFormat menu. Click the Number tab, and then click Percentage in the Categorybox.