Kako uporabljati funkcijo Excel COUNTIF -

Povzetek

COUNTIF je Excelova funkcija za štetje celic v obsegu, ki ustreza enemu pogoju. COUNTIF lahko uporabite za štetje celic, ki vsebujejo datume, številke in besedilo. Merila, uporabljena v COUNTIF, podpirajo logične operatorje (>, <,, =) in nadomestne znake (* ,?) za delno ujemanje.

Namen

Štejte celice, ki ustrezajo merilom

Vrnjena vrednost

Število celic, ki predstavljajo preštete celice.

Sintaksa

= COUNTIF (obseg, merila)

Argumenti

obseg - obseg celic za štetje.
Merila - Merila, ki nadzorujejo, katere celice je treba šteti.

Različica

Excel 2003

Opombe o uporabi

Funkcija COUNTIF v Excelu šteje število celic v obsegu, ki se ujema z enim dobavljenim pogojem. Merila lahko vključujejo logične operatorje (>, <,, =) in nadomestne znake (* ,?) za delno ujemanje. Merila lahko temeljijo tudi na vrednosti iz druge celice, kot je razloženo spodaj.

COUNTIF je v skupini osmih funkcij v Excelu, ki delijo logična merila na dva dela (obseg + merila). Posledično je sintaksa, ki se uporablja za konstruiranje meril, drugačna in COUNTIF zahteva obseg celic, matrike ne morete uporabiti.

COUNTIF podpira samo en pogoj. Če želite uporabiti več meril, uporabite funkcijo COUNTIFS. Če morate kot del logičnega preizkusa manipulirati z vrednostmi v argumentu obsega, glejte funkciji SUMPRODUCT in / ali FILTER.

Osnovni primer

Na zgornjem delovnem listu so v celicah G5, G6 in G7 uporabljene naslednje formule:

=COUNTIF(D5:D12,">100") // count sales over 100 =COUNTIF(B5:B12,"jim") // count name = "jim" =COUNTIF(C5:C12,"ca") // count state = "ca"

Obvestilo COUNTIF ne razlikuje med velikimi in malimi črkami, "CA" in "ca" se obravnavata enako.

Dvojni narekovaji ("") v merilih

Na splošno morajo biti besedilne vrednosti zaprte z dvojnimi narekovaji (""), številke pa ne. Če pa je logični operator vključen v številko, morata biti številka in operator vključena v narekovaje, kot je razvidno iz drugega spodnjega primera:

=COUNTIF(A1:A10,100) // count cells equal to 100 =COUNTIF(A1:A10,">32") // count cells greater than 32 =COUNTIF(A1:A10,"jim") // count cells equal to "jim"

Vrednost iz druge celice

Vrednost iz druge celice je mogoče vključiti v merila z uporabo združevanja. V spodnjem primeru bo COUNTIF vrnil število vrednosti v A1: A10, ki so manjše od vrednosti v celici B1. Upoštevajte, da je manj kot operator (to je besedilo) priložen narekovajem.

=COUNTIF(A1:A10,"<"&B1) // count cells less than B1

Ni enako

To construct "not equal to" criteria, use the "" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in the range A1:A10:

=COUNTIF(A1:A10,"red") // not "red"

Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=COUNTIF(A1:A10,"") // not blank =COUNTIF(A1:A10,"") // blank

Dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

=COUNTIF(A1:A10, ">"&B1) // count dates greater than A1

Notice we must concatenate an operator to the date in B1. To use more advanced date criteria (i.e. all dates in a given month, or all dates between two dates) you'll want to switch to the COUNTIFS function, which can handle multiple criteria.

The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this

=COUNTIF(A1:A10,"<"&DATE(2020,4,1)) // dates less than 1-Apr-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIF(A1:A5,"*apple*") // cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

=COUNTIF(A1:A5,"???") // cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.
Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "ja*"
Cell references in criteria are not enclosed in quotes, i.e. "<"&A1
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
COUNTIF requires a range, you can't substitute an array.
COUNTIF returns incorrect results when used to match strings longer than 255 characters.
COUNTIF will return a #VALUE error when referencing another workbook that is closed.