COUNTIF and COUNTIFS: build counts you can explain

A useful count starts with a precise question. “How many open tickets are there?” asks about one field. “How many open tickets belong to the West team?” combines two fields. “How many are open or belong to West?” is different again. A formula can calculate correctly while answering the wrong one.

COUNTIF and COUNTIFS preserve the source records and produce a summary. Work in an empty part of a copy first. Keep each record on one row, give the columns clear labels, and decide whether you are counting tickets, customers, or individual filled cells.

Choose the counting question

Microsoft defines the basic functions in its COUNTIF and COUNTIFS references. The distinction is logical, not a measure of how large the spreadsheet is.

Create a five-row example with known results

Use row 1 for the headings Ticket, Status, Region, and Amount in columns A:D. Enter these five records in rows 2:6: T1, Open, West, 120; T2, Closed, West, 80; T3, Open, East, 200; T4, Pending, West, 150; T5, Open, West, 60. The example uses invented records so you can check every match by eye.

1. In F2, enter =COUNTIF(B2:B6,"Open"). The expected result is 3.
2. In F3, enter =COUNTIFS(B2:B6,"Open",C2:C6,"West"). The expected result is 2.
3. In F4, enter =COUNTIFS(B2:B6,"Open",D2:D6,">=100"). The expected result is 2.
4. Inspect the qualifying rows for each question before copying the pattern into real data.

For the third formula, T1 and T3 qualify. T5 is open but its amount is below 100. COUNTIFS tests corresponding positions across its ranges, so B2:B6 and D2:D6 must describe the same records and have matching dimensions.

Use criteria cells and comparison operators

Put a threshold such as 100 in H2. Then =COUNTIF(D2:D6,">="&H2) counts amounts greater than or equal to that threshold. The expected answer is 3: 120, 200, and 150. The operator belongs inside quotation marks; the ampersand joins it to the value from H2.

When the criterion is simply text stored in H3, use =COUNTIF(B2:B6,H3). Do not quote H3 unless you literally want to search for those two characters. Clear labels beside input cells make it easier for someone else to change the question without editing the formula itself.

For a numeric band, =COUNTIFS(D2:D6,">=100",D2:D6,"<200") returns 2. Here the same amount range is tested twice. Microsoft’s conditional number and date examples describe this paired-boundary approach. State whether each boundary is inclusive so that adjacent bands do not overlap.

Handle OR conditions without counting twice

In this example, =COUNTIF(B2:B6,"Open")+COUNTIF(B2:B6,"Pending") returns 4. A ticket cannot have both of those single-cell status labels, so the two groups do not overlap.

Open or West is different. There are 3 open tickets and 4 West tickets, but adding them gives 7 for only 5 records. Subtract the 2 tickets in both groups: =COUNTIF(B2:B6,"Open")+COUNTIF(C2:C6,"West")-COUNTIFS(B2:B6,"Open",C2:C6,"West"). The correct result is 5. This example illustrates inclusion and exclusion; do not add many overlapping categories without mapping the logic first.

Check wildcards, blanks, and inconsistent labels

A criterion like "Open*" can match Open and Open - waiting because the asterisk is a wildcard. A question mark represents one character, and a tilde can escape a literal wildcard. Use exact labels when a broad pattern would group different business states.

COUNTIF text comparisons are not case-sensitive. Unwanted spaces can also make visually similar labels behave differently. Inspect unexpected zero counts rather than changing every label immediately. Blank criteria cells and formulas returning empty text need a deliberate rule; an unlabeled empty input should not silently define your report.

Define the unit before sharing the number

These examples count qualifying rows. If T1 appears twice because an export contains two updates for the same ticket, both rows can contribute. The result is then a count of matching records, not necessarily a count of distinct tickets. Compare a few identifiers with the export’s definition before labeling the result “open tickets.” Do not remove repeated rows until you know whether they represent accidental duplication or a legitimate history.

Also check the end of the range after each new export. A formula ending at row 6 will not include a ticket added in row 7. Keep the raw export intact, inspect the intended data boundaries, and update all corresponding criteria ranges together. Record the reporting date beside a saved result so readers can distinguish today’s question from a count calculated on an older snapshot.

Verify totals and know when to stop

Filter a few source rows manually and reconcile them with the formula. Keep headers outside the data range. Do not assume hiding rows changes these counts; if you need a visible-row summary, Microsoft’s counting-method guide discusses alternatives including SUBTOTAL.

A #VALUE! error can arise when COUNTIF or COUNTIFS refers to a closed external workbook. Microsoft documents this in its error reference. Stop if you cannot identify the source workbook, the unit being counted, or the overlap rules. Replacing errors with zero before resolving those questions can turn missing evidence into a misleading result.

Prepared with AI assistance from the linked documentation. This is a source-led explanation, not a claim of hands-on testing across every device. Read the editorial policy.