How does the SQL version of COUNTIFS work?

Every criteria pair becomes an AND condition in the WHERE clause. To produce several COUNTIFS results in one table, use COUNT(*) FILTER (WHERE ...) for each, as in a conditional aggregation. GROUP BY gives the count for each value of a column without writing a formula per value.

Why can SQL give a different result from COUNTIFS?

COUNTIFS criteria are always ANDed; SQL can express OR with parentheses.

COUNTIFS in SQL

COUNTIFS counts rows that meet several conditions at once; in SQL you combine the conditions in WHERE or use FILTER for several counts in one query.

The Excel version

=COUNTIFS(B:B, "West", C:C, ">500")

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_over_500
FROM sales
WHERE region = 'West'
  AND amount > 500;

How it works

Every criteria pair becomes an AND condition in the WHERE clause.
To produce several COUNTIFS results in one table, use COUNT(*) FILTER (WHERE ...) for each, as in a conditional aggregation.
GROUP BY gives the count for each value of a column without writing a formula per value.

Where the results differ from Excel

COUNTIFS criteria are always ANDed; SQL can express OR with parentheses.
Criteria like ">500" are text patterns in Excel; in SQL they are typed comparisons, so strings that look like numbers must be cast.
Blank cells in Excel become nulls in an imported table; they never match a comparison such as amount > 500.

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 West region orders were over 500?
For each region, how many orders were over 500 and how many were not?