COUNTIF Versus COUNT With a Filter in SQL Compared

Counting looks simpler than summing, and it hides more differences between Excel and SQL, because Excel has five count functions with different rules about what counts, and SQL has one function whose rules depend on what you pass it. This post lines them up, translates the common COUNTIF shapes and gives the distinct count that spreadsheets approximate with a clever formula. It is a spoke of translating Excel formulas into SQL.

The data

WITH tickets(id, team, status, opened, hours) AS (
  VALUES
    (1, 'A', 'open',   DATE '2024-03-01', 2.5),
    (2, 'A', 'closed', DATE '2024-03-02', 4.0),
    (3, 'B', 'open',   DATE '2024-03-03', NULL),
    (4, 'B', '',       DATE '2024-03-04', 1.0),
    (5, 'B', 'closed', NULL,              3.5),
    (6, 'A', NULL,     DATE '2024-03-06', 0.5)
)

Row 3 has no hours, row 4 has an empty status, row 5 has no opened date and row 6 has a null status. These gaps are what the count functions disagree about.

The five Excel counts and their SQL equivalents

Apply them to the hours column. COUNT(hours) is 5 (a null is not counted), and count(hours) in SQL is 5. For the status column, COUNTA in Excel counts every cell that is not truly empty (including a cell whose formula returns an empty string), while SQL's count(status) counts the empty string row and skips the null row: 5 for status. COUNTBLANK counts both the true blank and the empty string, which in SQL is rows 4 and 6: 2.

The practical rule is to be explicit. Decide what "empty" means for the column, and write the condition: status IS NULL OR status = '', or normalize on load so that empty strings become NULL and one test suffices.

COUNTIF: count star with a condition

=COUNTIF(C:C, "open") counts the open tickets:

SELECT count(*) FROM tickets WHERE status = 'open';

SELECT count(*) FILTER (WHERE status = 'open') AS open_tickets FROM tickets;

Both return 2. Use count(*) to count rows and count(column) to count non-null values; with a WHERE or FILTER, count(*) is the right choice for "how many rows meet this". A common translation error is to write count(status) with a filter on a different column, which silently skips rows where the counted column is null.

Several COUNTIFs over a list of categories are one GROUP BY:

SELECT status, count(*) AS n
FROM tickets
GROUP BY status
ORDER BY status NULLS LAST;

 status | n
--------+---
        | 1
 closed | 2
 open   | 2
        | 1

The empty string and the null are separate groups, which is useful: the workbook's COUNTIF(C:C, "") mixes them, and you can see here that there are two kinds of empty. The group list comes from the data, so a new status appears automatically, where the spreadsheet needs someone to type the new label into the list.

COUNTIFS: several conditions

=COUNTIFS(B:B, "B", C:C, "open") counts the tickets of team B that are open:

SELECT count(*) FROM tickets WHERE team = 'B' AND status = 'open';

The answer is 1 (row 3). To report several such counts in one row, use FILTER:

SELECT
  count(*) FILTER (WHERE team = 'A') AS team_a,
  count(*) FILTER (WHERE team = 'B' AND status = 'open') AS b_open,
  count(*) FILTER (WHERE opened >= DATE '2024-03-03') AS since_march_3
FROM tickets;

which returns 3, 1 and 3. The last count excludes row 5, whose date is null: a comparison with null is not true. That matches Excel, where a blank cell does not satisfy ">=" & date.

Criteria with operators translate as for sums (SUMIF and SUMIFS versus GROUP BY): ">2" is hours > 2, "N*" is LIKE 'N%', and the case rules apply: Excel ignores case and PostgreSQL does not.

Distinct counts

Counting how many different values a column holds is awkward in Excel. The traditional formula is =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)), which divides each row by the number of times its value appears, so each value contributes a total of one. It is slow, breaks on blanks (a division by zero) and gets slower with the square of the row count. Newer Excel has =COUNTA(UNIQUE(A2:A100)). In SQL:

SELECT count(DISTINCT team) AS teams,
       count(DISTINCT status) AS statuses
FROM tickets;

The results are 2 teams and 3 statuses. The three statuses are open, closed and the empty string: count(DISTINCT status) skips the null row but counts '' as a value. If the empty string is not a real status, normalize it first with NULLIF(status, ''), which turns it into a null so it is skipped:

SELECT count(DISTINCT NULLIF(status, '')) AS statuses FROM tickets;

That returns 2. This is the kind of difference that makes a translated count off by one against the workbook, and the fix is to decide what empty means before counting.

Counting distinct combinations (team and status together) uses a subquery: SELECT count(*) FROM (SELECT DISTINCT team, status FROM tickets) t;, since count(DISTINCT a, b) is not portable.

Counts after a join

A count taken after a join counts the rows of the joined result. If tickets are joined to a table with several rows per ticket (comments, assignments), count(*) is the number of joined rows, not tickets. Count distinct tickets: count(DISTINCT t.id), or count in a subquery before joining. The spreadsheet analog is a COUNTIF over a column of a lookup result that repeats ticket ids. When a translated count is far larger than the workbook's, look for a one-to-many join upstream.

Percentages and shares

=COUNTIF(C:C, "open")/COUNTA(C:C) gives a share. In SQL, divide two counts, and cast to avoid integer division:

SELECT count(*) FILTER (WHERE status = 'open')::numeric / count(status) AS open_share FROM tickets;

With 2 open tickets and 5 non-null statuses, the result is 0.4. Note again the choice of denominator: count(status) skips the null row and counts the empty string, count(*) counts all six rows (giving 0.3333). The workbook's COUNTA and your count(*) are not the same denominator, and the percentage differs. State the denominator in words ("open tickets as a share of tickets with a status") and write the SQL to match.

Verify

For a sheet of tickets loaded from Excel, the staging steps are in getting Excel data into a database. Run each translated count and compare with the workbook's cached values. Check the null and empty-string rows explicitly: count them separately and make sure the translation treats them as the workbook does. Reconcile the group counts with the total: sum(n) over the grouped query must equal count(*).

When COUNTIF should stay in the spreadsheet

The count is a small part of an interactive sheet. A dashboard cell that counts the rows a person has filtered is part of the interface.

The condition is a formula result that you cannot reproduce. If the criterion comes from a hard-to-read cell, understand it before translating.

The data is not in a database. Loading a hundred rows to count them is overkill.

The count is of colored cells or comments. Excel's formulas cannot see color without macros, and the database has no color. The information was never data.

Excel documents the functions in its pages on COUNTIFS and the related counting functions, and PostgreSQL describes count in its aggregate functions reference. To check a small extract as JSON, use the CSV to JSON converter.