SUMIF and SUMIFS Versus GROUP BY in SQL Compared in Depth

SUMIF is how most people do their first conditional total, and a workbook that has grown for a few years usually holds hundreds of them. They look different from SQL and say the same thing: add up one column for the rows where another column meets a condition. This post translates SUMIF and SUMIFS into SQL in each of the shapes they appear in, and lists the places where the answers differ. It is a spoke of translating Excel formulas into SQL.

The data

WITH sales(id, region, product, amount, sold) AS (
  VALUES
    (1, 'North', 'pen',      20.00, DATE '2024-03-01'),
    (2, 'North', 'notebook', 35.00, DATE '2024-03-04'),
    (3, 'South', 'pen',      15.00, DATE '2024-03-06'),
    (4, 'South', 'notebook', 60.00, DATE '2024-03-10'),
    (5, 'North', 'pen',      25.00, DATE '2024-03-12'),
    (6, 'East',  'pen',      10.00, NULL)
)

In Excel this sits in columns A to E of a sheet, with regions in B, products in C and amounts in D.

One SUMIF: a WHERE or a FILTER

=SUMIF(B:B, "North", D:D) adds the amounts where the region is North. Three SQL forms give the same number, 80.00 (20 plus 35 plus 25):

SELECT sum(amount) FROM sales WHERE region = 'North';

SELECT sum(amount) FILTER (WHERE region = 'North') FROM sales;

SELECT sum(CASE WHEN region = 'North' THEN amount END) FROM sales;

Use WHERE when the condition applies to the whole query, and FILTER or CASE when several conditional sums sit in the same result row, as in a summary line with a total for each region. The CASE form works on every engine, and FILTER is standard but not universal; SQL Server lacks it.

Many SUMIFs: a GROUP BY

When the same SUMIF is copied down a list of categories, as =SUMIF($B:$B, F2, $D:$D) next to a column of region names, the whole block is one query:

SELECT region, sum(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

 region | total
--------+-------
 East   | 10.00
 North  | 80.00
 South  | 75.00

The difference is where the category list comes from. In the sheet, someone typed or pasted the distinct regions into F2:F4, and a new region in the data does not appear until they add it, which is a classic source of totals that do not add up to the grand total. GROUP BY produces the list from the data, so the total always reconciles. A check worth keeping in the spreadsheet too: the sum of the SUMIF block should equal SUM(D:D).

SUMIFS: several conditions

=SUMIFS(D:D, B:B, "North", C:C, "pen") takes the sum range first and then pairs of criteria range and criterion. All pairs must be true:

SELECT sum(amount)
FROM sales
WHERE region = 'North' AND product = 'pen';

The result is 45.00 (20 plus 25). Note the argument order: SUMIF puts the sum range last and SUMIFS puts it first, a source of wrong formulas that the SQL, with its named columns, avoids.

OR logic is where Excel gets clumsy. To add the North and South totals, a workbook uses either two SUMIFS added together or SUM(SUMIFS(D:D, B:B, {"North","South"})). In SQL it is a membership test:

SELECT sum(amount) FROM sales WHERE region IN ('North', 'South');

which returns 155.00. For a mixed condition, such as region North or product notebook, write WHERE region = 'North' OR product = 'notebook' and take care with parentheses when combining AND and OR; a SUMIFS plus a SUMIFS minus their overlap is the spreadsheet's way to avoid counting a row twice, and SQL does it in one pass without needing the correction.

Criteria strings

The criterion in a SUMIF can be a comparison written as text, and each form has a SQL operator:

Two of these hide traps. The criterion "<>North" in Excel includes blank cells, because a blank is not equal to North. In SQL, region <> 'North' excludes rows where region is NULL, since the comparison is unknown. To match Excel, write region IS DISTINCT FROM 'North' (PostgreSQL) or region <> 'North' OR region IS NULL. And the empty-string criterion matches both truly empty cells and cells holding an empty string, so the SQL needs both tests.

Wildcards: Excel's * and ? correspond to SQL's % and _. A literal asterisk in the data is escaped in Excel with a tilde ("~*") and in SQL with an ESCAPE clause or a different pattern.

Dates as criteria

A date criterion works in Excel when the cells are real dates: =SUMIFS(D:D, E:E, ">="&DATE(2024,3,5), E:E, "<"&DATE(2024,3,12)). In SQL:

SELECT sum(amount) FROM sales
WHERE sold >= DATE '2024-03-05' AND sold < DATE '2024-03-12';

The result is 75.00 (15 plus 60). The half-open range (>= start, < end) is the safe habit, because it also works for timestamps. Row 6, with no sale date, is excluded in both: a blank date is not in range. If the dates in the workbook are text, SUMIFS on a date criterion silently misses them, and you will have a total that is too low; in SQL they must be cast, and a failed cast is an error, which makes the problem visible (dealing with text that looks like a date in Excel).

Case, collation and spaces

Excel's SUMIF ignores case: "north" matches North. PostgreSQL's = on text is case-sensitive, so WHERE region = 'north' returns nothing and the sum is NULL. If the workbook relies on case-insensitive matching, translate with lower(region) = 'north', ILIKE, or a case-insensitive collation or citext column. Spaces matter in the other direction: "North " with a trailing space does not match "North" in Excel either, but nobody can see it, and the workbook's author may have fixed it with TRIM in a helper column. Normalize text on load so the conditions mean what they look like.

Sums that come out different

When the SQL and the workbook disagree, these are the usual causes.

A short range. =SUMIF(B2:B500, "North", D2:D500) stops at row 500 and the data has 845 rows. The workbook undercounts. SQL will not reproduce the error, so the SQL total is higher; the workbook was wrong.

Mismatched ranges. In SUMIF, if the sum range is a different size than the criteria range, Excel resizes it from its first cell, which can sum the wrong cells without a warning. SUMIFS requires equal sizes and returns an error otherwise.

Numbers stored as text. A SUMIF skips cells whose amount is text. The total looks right and the row is missing. In SQL, loading amounts into a numeric column rejects the text, which exposes the rows.

Hidden rows and filters. SUMIF includes hidden rows; SUBTOTAL does not. If a workbook uses a filter and SUBTOTAL, the equivalent SQL needs a condition that says which rows are visible, and that is information the database may not have.

Floating point. The workbook sums doubles; PostgreSQL's numeric is exact. Round both to the cent before comparing.

Verify

Load the data, run the grouped query, and compare each total with the workbook's cached SUMIF results (reading only the values and not the formulas). Check that the grouped totals add up to the grand total, and look at the count of rows in each group with count(*) beside the sums.

When SUMIF is a better fit than a SQL query

The criterion is chosen by a person in a cell. A dropdown that changes the region and the total is an interactive report. Keep the formula, and feed it from the database.

The data is small and lives in the workbook. For a few hundred rows, there is nothing to gain.

The totals sit inside a larger layout. A formatted statement with subtotals in specific cells is a presentation. Generate the numbers in SQL and place them, rather than rebuilding the layout.

The criteria are produced by formulas that are hard to state. Conditions built by string concatenation inside the criterion deserve to be understood, rewritten as columns, and then translated.

The function is described in Microsoft's pages on SUMIFS, and PostgreSQL's aggregate FILTER clause in its expressions reference. To check a small extract as JSON, use the CSV to JSON converter.