How does the SQL version of COUNTIF work?

WHERE region = 'West' filters the rows, and COUNT(*) counts those that remain. For counts of every value at once, as a column of COUNTIFs would give, use GROUP BY region with COUNT(*). COUNT(column) counts only non-null values, similar to how COUNTA skips blank cells.

Why can SQL give a different result from COUNTIF?

COUNTIF with a wildcard like "W*" corresponds to LIKE 'W%' in SQL.

COUNTIF in SQL

COUNTIF counts the cells that meet a condition; in SQL you use COUNT with a WHERE clause.

The Excel version

=COUNTIF(B:B, "West")

The SQL equivalent

This example assumes: sales(id bigint, region text, amount numeric, sale_date date). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT COUNT(*) AS west_orders
FROM sales
WHERE region = 'West';

How it works

WHERE region = 'West' filters the rows, and COUNT(*) counts those that remain.
For counts of every value at once, as a column of COUNTIFs would give, use GROUP BY region with COUNT(*).
COUNT(column) counts only non-null values, similar to how COUNTA skips blank cells.

Where the results differ from Excel

COUNTIF with a wildcard like "W*" corresponds to LIKE 'W%' in SQL.
COUNTIF ignores case; SQL equality does not in PostgreSQL.
COUNTIF over a duplicated list counts each duplicate; to count unique values use COUNT(DISTINCT column).

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

How many orders came from the West region?
How many orders did each region have?