Excel COUNTIF: Funktionssyntax und Verwendung von COUNTIF mit Kriterien

Excel COUNTIF: Function Syntax and Using COUNTIF with Criteria

Excel's COUNTIF function is a versatile tool for counting cells that meet specific criteria within a specified range. The COUNTIF function has a simple syntax and can be used with various types of criteria.

Syntax

The syntax of the COUNTIF function is:

=COUNTIF(range, criteria)

Here, range refers to the range of cells to be counted, and criteria specifies the condition for counting cells. The range and criteria arguments can be numbers, text strings, cell references, or expressions. For example, the formula

=COUNTIF(C2:C15, "Roger Federer")

counts the number of times the text "Roger Federer" appears within the range C2:C15.

Using COUNTIF with Criteria

COUNTIF supports various logical operators and wildcards for partial matching. Here are some examples of how to use COUNTIF with different criteria:

• Cells greater than 75: ">75"
• Cells equal to 100: 100 or "100"
• Cells less than or equal to 100: "<=100"
• Cells equal to "Red": "red"
• Cells not equal to "Red": "<>red"
• Cells that are blank: ""
• Cells that are not blank: "<>"
• Cells that begin with "X": "x*"
• Cells less than A1: "<"&A1
• Cells less than today: "<"&TODAY()

Note that if you are using a value from another cell or the result of a formula in criteria with a logical operator like "<", you will need to concatenate the value with the operator using the ampersand (&) character. For example, to count cells in the range B5:B16 that are less than the value in cell B1, you would use the formula:

=COUNTIF(range, "<"&B1)

Double Quotes in Criteria

In general, text values need to be enclosed in double quotes (""), and numbers do not. However, when a logical operator is included with a number, both the number and operator must be enclosed in quotes. For example:

• Count cells equal to 100: =COUNTIF(range, 100)
• Count cells greater than 32: =COUNTIF(range, ">32")
• Count cells equal to "jim": =COUNTIF(range, "jim")

Value from Another Cell

To use a value from another cell in criteria, you can use concatenation. For example, to count values in the given range that are less than the value in cell B1, you would use the formula:

=COUNTIF(range, "<"&B1)

Not Equal to

To construct "not equal to" criteria, you can use the "<>" operator. For example:

=COUNTIF(range, "<>"&B1)

This formula will count cells in the range that are not equal to the value in cell B1.

Erfahren Sie, wie die Excel-Funktion COUNTIF verwendet wird, um Zellen zu zählen, die bestimmten Kriterien innerhalb eines festgelegten Bereichs entsprechen. Die COUNTIF-Funktion hat eine einfache Syntax und unterstützt verschiedene Arten von Kriterien. Lernen Sie, wie Sie logische Operatoren und Platzhalter verwenden, um spezifische Zellen effektiv zu zählen.