Why does my COUNTIF return 0 when I can see matching values in the range?

The most likely cause is a data type mismatch. If your criterion is a number and the range contains numbers formatted as text (common in CSV imports from Salesforce or ERP systems), COUNTIF finds no matches and returns 0 with no error. Confirm with `=ISNUMBER(C2)` on a sample cell - if it returns FALSE for what looks like a number, the column is text. Fix with `=VALUE()` wrapping or the Data > Split text to columns trick (select the range, run the split, then immediately cancel the dialog). As of June 2026, this is still the most common silent failure in COUNTIF formulas built on imported data.

How do I write a COUNTIF criterion that combines a comparison operator with a cell reference?

Put the operator inside quotes and concatenate the cell reference outside: `">="&Assumptions!$B$3`. Never embed the reference inside the quotes - `">=Assumptions!$B$3"` treats the entire string as literal text and returns 0. This concatenation pattern works for all comparison operators: `>`, ` =`, ` `.

Can COUNTIF reference a range on a different tab?

Yes. The syntax is `=COUNTIF('SheetName'!ColumnRange, criterion)`. Single quotes around the sheet name are required whenever the name contains spaces, ampersands, or other special characters - so `'P&L'!B:B` needs the quotes, `Revenue!B:B` doesn't. If the sheet name itself contains an apostrophe, escape it by doubling it inside the quotes: `'Joe''s Model'!B:B`.

What's the difference between COUNTIF and COUNTIFS when I only have one condition?

Functionally identical for a single condition - `=COUNTIF(range, criterion)` and `=COUNTIFS(range, criterion)` return the same result. Use `COUNTIF` for one condition and switch to `COUNTIFS` the moment you need two or more. `COUNTIFS` applies AND logic across all criterion pairs: every condition must match for a row to be counted.

How do I count cells that are not blank with COUNTIF?

`=COUNTIF(range, "<>")` counts all non-empty cells, equivalent to `COUNTA(range)` for most purposes. To count non-blank cells within a specific segment, combine it into `COUNTIFS`: `=COUNTIFS('Pipeline'!C:C, "Enterprise", 'Pipeline'!D:D, "<>")` counts enterprise accounts where column D is populated. This pattern is useful for data completeness checks - for example, confirming that every enterprise account in a DCF scenario has a deal size entered.

Google Sheets COUNTIF Range & Criterion Syntax (2026)

Your board pack shows 0 enterprise accounts. Your contribution margin analysis shows no qualifying SKUs. No error flag, no #VALUE! - just a convincing-looking zero that's wrong. That's what a criterion type mismatch looks like in production.

The Criterion Argument: Four Forms

The criterion can be written four ways, and each has a specific context where it belongs.

Form	Example	Use When
Number	`=COUNTIF('P&L'!C:C, 0)`	Literal zeros, numeric IDs, codes stored as numbers
Text string	`=COUNTIF('P&L'!B:B, "Enterprise")`	Exact text match; case-insensitive
Comparison string	`=COUNTIF('Revenue'!D:D, ">="&Assumptions!$B$3)`	Greater/less than with a cell reference for the threshold
Wildcard	`=COUNTIF('GL'!A:A, "4*")`	Partial matches; `?` for a single character, `*` for any sequence

The rule that trips most people: when you combine a comparison operator with a cell reference, the operator goes inside quotes and you concatenate the reference outside. ">="&Assumptions!$B$3 is correct. ">=Assumptions!$B$3" is not - that treats the whole string as literal text and returns zero.

COUNTIF Across Tabs: The Syntax That Actually Matters

In a model with 8+ linked sheets, you're almost never counting within the same tab. The range argument supports full cross-sheet references: 'SheetName'!Column:Column.

Count enterprise accounts in your pipeline tab to size a DCF scenario:

=COUNTIF('Pipeline'!C:C, "Enterprise")

Count revenue periods that exceed a threshold pulled from your Assumptions tab:

=COUNTIF('Revenue'!D:D, ">="&Assumptions!$B$3)

For a bank syndicate DCF where you need to count quarters above a hurdle rate across your FCFF tab:

=COUNTIF('FCFF'!B5:B24, ">="&Assumptions!$C$12)

The $ locking on the criterion cell matters. If you're building a sensitivity table and dragging formulas, an unlocked Assumptions reference drifts - and you won't catch it until someone asks why Q3 and Q4 show the same count.

COUNTIF vs. COUNTIFS: When to Switch

COUNTIF handles one condition. The moment you need two - account type AND revenue above threshold - switch to COUNTIFS.

COUNTIFS syntax: =COUNTIFS(range1, criterion1, range2, criterion2, ...).

Count enterprise accounts with ARR above $250K for a board pack segment:

=COUNTIFS(
  'Accounts'!C:C, "Enterprise",
  'Accounts'!D:D, ">="&Assumptions!$B$5
)

Count new hires in Q3 above a salary band for a runway sensitivity:

=COUNTIFS(
  'Headcount'!E:E, ">="&DATE(2026,7,1),
  'Headcount'!E:E, "<="&DATE(2026,9,30),
  'Headcount'!F:F, ">="&Assumptions!$D$8
)

Note the date range pattern: two conditions on the same column, one per boundary. This is how you count observations within a quarter without a helper column. COUNTIFS uses AND logic - all criteria must match for a row to count.

For scenarios where you need to count across entire arrays or return per-row match counts, ARRAYFORMULA combined with COUNTIF handles cases that COUNTIFS alone can't address cleanly.

The Three Silent Failures

These don't throw errors. They return zero. That's what makes them expensive.

Numbers stored as text. The most common silent failure in imported data - anything pulled from Salesforce, a data warehouse export, or an ERP CSV. =COUNTIF('Revenue'!C:C, ">="&50000) returns 0 if column C contains text-formatted numbers, even though they look like $50,000 in the cell. =ISNUMBER(C2) on a sample cell will confirm whether they're real numbers. Fix with =VALUE() wrapping, or run Data > Split text to columns and immediately cancel - this forces type conversion on the selected range.

Date criterion format. Never write a date as a string in a COUNTIF criterion. "<=6/30/2026" behaves unpredictably across locales - a colleague in London or a model opened with European regional settings can silently break it. Always use DATE() or concatenate against a cell reference: "<="&DATE(2026,6,30) or "<="&Assumptions!$B$2 where B2 holds a properly formatted date value.

Wildcard escaping. If you're counting GL account descriptions that contain a literal asterisk - common in markup rate notations like "Cost * 1.15" - the * in your criterion acts as a wildcard, matching everything. Escape it with a tilde: "~*" matches a literal asterisk. So =COUNTIF('GL'!B:B, "*~**") finds descriptions containing any text, then a literal asterisk, then any text.

A Validation Block for Your Assumptions Tab

Here's the debugging pattern worth building once and reusing: a 3-row audit block you park in a corner of your Assumptions tab and point at any COUNTIF that's returning suspicious zeros.

The block checks three things in sequence - is the range populated, does the criterion match anything, and do the data types agree? Replace 'P&L'!B:B with your actual range and Assumptions!$B$3 with your criterion cell:

// Row 1: Is the range populated?
=IF(COUNTA('P&L'!B:B)>1,
  "✓ " & COUNTA('P&L'!B:B)-1 & " rows",
  "⚠ RANGE EMPTY")

// Row 2: Does the criterion produce any matches?
=IF(
  COUNTIF('P&L'!B:B, Assumptions!$B$3)=0,
  "⚠ ZERO MATCHES - check criterion type or spelling",
  "✓ " & COUNTIF('P&L'!B:B, Assumptions!$B$3) & " matches"
)

// Row 3: Do the data types agree?
=IF(
  ISNUMBER(Assumptions!$B$3) = ISNUMBER(INDEX('P&L'!B:B, 2)),
  "✓ Types match (" & IF(ISNUMBER(Assumptions!$B$3), "both numeric", "both text") & ")",
  "⚠ TYPE MISMATCH - criterion is " &
    IF(ISNUMBER(Assumptions!$B$3), "numeric", "text") &
    ", range values are " &
    IF(ISNUMBER(INDEX('P&L'!B:B, 2)), "numeric", "text")
)

Row 1 confirms the range has data. Row 2 confirms the criterion matches at least one cell. Row 3 is the diagnostic: it compares ISNUMBER() on your criterion cell against ISNUMBER() on the first data cell sampled via INDEX() - so it tells you exactly which side has the wrong type.

If Row 2 flags "ZERO MATCHES" and Row 3 flags "TYPE MISMATCH," you've found the bug in about 10 seconds. If both rows are clean but you're still getting unexpected zeros, the problem is almost always a hidden trailing space in the criterion - wrap it with TRIM(Assumptions!$B$3) to test.

For a model with multiple COUNTIF-driven outputs feeding scenario analysis or segment summaries, duplicate this block for each critical formula and label them by what they're auditing. Three rows per check is a small overhead against the cost of a wrong number making it into a board deck.

Using AI to Build and Audit These Formulas

Writing COUNTIF variants across a multi-tab model is tedious enough. Debugging which tab has a type mismatch when you're 4 hours into model build is worse. ModelMonkey - the AI assistant that runs inside Google Sheets - can scan a column, identify whether values are stored as numbers or text, and flag where a COUNTIF criterion would return zero due to a type mismatch. It's a faster path to the same diagnostic the audit block above provides, particularly when you're touching ranges across tabs you didn't build.