COUNTIFS not working: why it returns 0 or #VALUE!

#VALUE!: the ranges are different sizes

Every criteria range in COUNTIFS must have the same number of rows and columns. COUNTIFS checks row 1 of each range together, then row 2, and so on, so B2:B9 and C2:C10 cannot be paired up. Excel refuses with #VALUE! rather than guess.

It usually creeps in when you edit one range after adding data, or click a range with the mouse and grab an extra row. Press F2 on the cell and compare the coloured boxes. Converting the data to a table (Ctrl+T) and using column names keeps all the ranges the same length as rows are added.

COUNTIFS also returns #VALUE! when it points at another workbook that is closed. Open the source file, or move the data into the same workbook.

0: the criterion compares against the text ">H2"

You want tickets that took more than the hours in H2 (5). Writing ">H2" in quotes makes it plain text, so COUNTIFS looks for values greater than the letters H2, finds none among the numbers, and returns 0 with no error.

Keep the operator in quotes and join the cell reference with &: ">"&H2. The same goes for dates and functions: ">="&DATE(2026,10,1) or "<"&TODAY(). A criterion without a cell, such as ">5", stays entirely inside the quotes. Leaving the quotes off (>5 on its own) is a syntax error and Excel will not accept the formula.

Hidden trailing spaces

There are six open tickets, but =COUNTIFS(C2:C9,"Open") returns 4. Two status cells actually hold "Open " with a space at the end, which is common in exports and pasted data. To COUNTIFS "Open " and "Open" are different values. Check with =LEN(C4): it returns 5 for a four-letter word.

The quick fix is a wildcard, "Open*", which matches Open followed by anything. That is fine here, but it would also count "Opened" or "Open - waiting". The proper fix is to clean the column: put =TRIM(C2) in a helper column, fill down, and paste it back as values. COUNTIFS cannot apply TRIM itself; if you cannot change the data, use SUMPRODUCT, which can: =SUMPRODUCT((B2:B9="Ana")*(TRIM(C2:C9)="Open")) also gives 2.

Dates stored as text are skipped

Counting October tickets with a date range should give 7, but the formula below returns 5. Two dates in column D came in as text ('2026-10-14 and '10/23/2026). They look like dates, but a comparison such as ">="&DATE(2026,10,1) only counts real numbers, so text dates are silently left out.

Spot them with =COUNT(D2:D9), which returns 6 for 8 rows: two cells are not numbers. =SUMPRODUCT(--ISTEXT(D2:D9)) counts them directly (2). Text dates usually sit on the left of the cell. Convert the column with Data > Text to Columns, or with DATEVALUE in a helper column, and the count becomes 7.

Wildcards do not match numbers

Order numbers starting with 10 belong to the online store. =COUNTIFS(F2:F9,"10*") looks right but returns 1. Wildcards (* and ?) only match text, so the real numbers 10482, 10517, 10560 and 10602 are ignored. The one match is F4, an order number stored as text.

For whole numbers in a known range, use two numeric criteria instead: =COUNTIFS(F2:F9,">=10000",F2:F9,"<20000") gives 4, which still misses the text one. To catch numbers and text together, test the first characters with SUMPRODUCT and LEFT, which treats every value as text.

Two criteria on one column means AND, not OR

COUNTIFS counts rows where every condition is true at once. =COUNTIFS(B2:B9,"Ana",B2:B9,"Ben") asks for rows where the agent is Ana and also Ben, which is impossible, so the answer is 0. Using the same range twice only makes sense for between conditions, such as ">=10000" and "<20000".

For OR, give COUNTIFS an array constant of the values and wrap it in SUM. Each value is counted separately and SUM adds the counts. The other criteria still apply to both: =SUM(COUNTIFS(B2:B9,{"Ana","Ben"},C2:C9,"Open*")) gives 4 open tickets for Ana or Ben. Adding two COUNTIFS with + does the same thing and is easier to read when there are only two values.