How we made exported Excel formulas count the same rows as PostgreSQL

Build notes is a weekly series about one change that went live in Every Number that week: what was wrong, how it works, what we measured, and what we didn't do. No. 1 is Excel with live formulas, released on November 1, 2026.

On a dashboard, choose Take it, then Excel with live formulas, and you get an .xlsx where most numbers are live Excel formulas over your rows. The hard part was making each formula count the same rows our database counts. Excel and SQL treat letter case, blank cells, wildcards and TRUE differently, and most of our fixes work in any Excel workbook.

What was wrong with a CSV

Until November 1, that menu item was CSV for Excel: each number with its definition, rows used and receipt ID, as plain values. Excel could read them but not recount them.

Rebuild a count by hand and the numbers drift. Say your orders sheet has 12 rows. In the Status column, 6 say paid, 1 says Paid, 1 is empty, and the other 4 say refunded or pending. The dashboard counts 6 paid orders. =COUNTIF(B2:B13,"paid") returns 7, because COUNTIF ignores upper and lower case. Both counts follow a rule, and a CSV of values can't show which one.

How the file works

The file has 2 sheets. Data holds the rows our calculation reads, with your team's fixes applied, such as a value renamed from Salse to Sales. Each column gets a defined name, so a formula reads like its definition: SUM(Amount), or SUMPRODUCT(--EXACT(Status,"paid")) for paid orders.

Those names follow Excel's rules: they ignore case, can't look like a cell reference, and can't be C or R. So status next to Status becomes status_2, and Q4, FY2026 and C become _Q4, _FY2026 and _C.

Rules that leave rows out go into every formula. If your team leaves out rows whose Note is test row and counts each email once, the row count becomes SUMPRODUCT(--(EXACT(Note,"test row")+(Repeat_Email=1)=0)). Repeat_Email is a Data column with a 1 on each row whose email appeared higher up.

Numbers has a row per dashboard number: Value (the live formula), Rows used (also a formula), Every Number (the value we counted), Matches, Definition, Note and Receipt ID. Matches is a formula too: IF(ABS(B9-D9)<=0.000000001*MAX(1,ABS(D9)),"Yes","No"). It allows a gap of about one billionth, because PostgreSQL adds numeric values exactly while Excel works in double precision with 15 digits.

The formulas aren't translated from our SQL. Both come from the same metric spec, a small JSON object saying what to count and which rows qualify. An AI model (Claude Haiku) may suggest it from your goal and column names, without seeing rows or values. Our compiler turns the spec into SQL, and PostgreSQL counts each number from the rows of one upload, with every value passed as a query parameter. Separate code turns the same spec into formulas.

Where Excel and SQL count differently

Each row is a rule our SQL follows, next to the formula that copies it.

Letter case

PostgreSQL's standard collations are deterministic: strings are equal only if they're the same bytes, so paid and Paid differ. Microsoft's documentation says COUNTIF ignores case and EXACT doesn't, so every text "equals" in our export is EXACT: paid orders are SUMPRODUCT(--EXACT(Status,"paid")), not a COUNTIF.

Blank cells

Our parser turns a blank cell into NULL, and in PostgreSQL a comparison with NULL yields null, not true or false. Our SQL counts with count(*) filter (where …), which keeps only rows where the condition is true, so an empty Amount is never 0 and never under 50.

In Excel, blank handling depends on the function: COUNTIFS treats an empty criteria cell as 0, and AVERAGE skips empty cells but counts zeros. Our formulas don't rely on either. Every comparison on a number or date column first checks that the cell isn't empty, as in (Due<>"")*(Due<=Data_through), where Data_through names the Data through cell.

Averages divide only by rows that have a value, like PostgreSQL's avg, which uses only non-null values. A median, minimum or maximum with a filter becomes an array formula that skips blanks too: MEDIAN(IF(--EXACT(Status,"paid")*(Amount<>""),Amount)).

Not equal

We decided "Status isn't paid" includes rows with no status. Plain SQL <> would drop them, because NULL <> 'paid' is null, so our compiler writes IS DISTINCT FROM, which is true when only one side is null. In the 12-row example, that's 6 rows, not 5. The formula is SUMPRODUCT(1-EXACT(Status,"paid")). For numbers, SUMPRODUCT(1-(Leads<>"")*(Leads=0)) counts an empty Leads cell as not 0.

Wildcards

"Note contains rush" is ILIKE in SQL and SEARCH in Excel, and both ignore case. Their wildcards differ: % and _ in SQL patterns, ? and * in SEARCH, where a tilde before one finds the actual character. Each side escapes the value its own way: our SQL puts a backslash before %, _ and \, and the formula puts a tilde before *, ? and ~.

TRUE and FALSE

In SQL, FILTER takes a condition as it is. In Excel, EXACT returns TRUE or FALSE, and SUMPRODUCT treats entries that aren't numbers as zeros, so =SUMPRODUCT(EXACT(Status,"paid")) returns 0. Microsoft's guide to conditional calculations multiplies conditions, which makes them evaluate as 1 or 0. Our formulas multiply conditions too, and a lone condition gets -- in front.

Numbers that stay values

Some numbers stay values, with the reason in the Note column: counts of different values, counts and shares when one record takes several rows (an order with line items, counted once), and the earliest or latest date. So does any number whose formula would pass 8,000 characters (Excel's limit is 8,192) or hold a quoted value over 255 characters, which a tilde before every wildcard can cause.

What we measured

Our tests export the file, then recalculate every formula with a small evaluator we wrote that models how Excel treats blanks, letter case, TRUE and FALSE, and wildcards. Each Value and Rows used must match what PostgreSQL counted, and Matches must say Yes.

The first test file has 12 orders built to trip up Excel: paid and Paid, blank and zero amounts, a negative amount, blank dates, and notes with *, ?, ~ and :(. All 35 numbers matched, before and after adding team rules (a renamed value, a corrected cell, a left-out row, each email once). On our 6 sample datasets, all 26 dashboard numbers became formulas and matched. A 6,000-row file exported whole and matched. All 7 test cases passed again on November 4, 2026.

We found 2 bugs before release. When we read each row as one array of text, the database driver returned empty values as the word NULL, which would have filled blank Data cells. The tests caught it, and the export now reads each column separately. Then a filter value with a stray parenthesis, x(, made a formula close early and return 0 where the database counted 2. The fix skips parentheses inside quotes, and 2 of the 35 test numbers cover it.

What we didn't do

• Check in Excel itself. The evaluator is our code, following Excel's rules as we understand them. We haven't opened these files in Microsoft Excel yet, and we make no claim about Google Sheets or Apple Numbers.
• Make every viewer recalculate. The workbook sets the Open XML flag fullCalcOnLoad (Full Calculation On Load), but each formula cell also stores our number as its cached result, and Matches stores Yes. A program that doesn't recalculate shows those, not its own count.
• Keep the repeat column live. Repeat_Email is fixed at export, so editing an email on the Data sheet won't move the 1s.
• Test case rules beyond English. ILIKE follows the database's locale, and our test file is in English, so letters like é are untested.
• Sync back. The file is a copy. Edits stay in it.

Where Every Number fits

If someone on your team rechecks numbers in Excel, this file gives them each formula and a Matches column. You can try it with your own file first, with no account: Excel or CSV up to 10 MB, deleted after 24 hours unless you save it. The Excel file is in Take it once the dashboard is saved to a team, and teams that sign up on their own are free for the first 30 days. How it works covers the rest, and Anatomy of a receipt explains each receipt.

It's a poor fit if you need distinct counts as formulas or a file that stays connected to the dashboard. If 2 numbers still disagree, Why dashboard numbers don't match lists 9 common causes.