SUMIF/COUNTIF Formula Builder

The request always arrives as a sentence: "I need the total revenue for the West region." Twenty minutes later it has become a scroll of half-built formulas — a SUM with plus signs between twelve cell references, a filter applied and a status-bar sum read by hand, a SUMIF that returns 0 for reasons nobody in the thread can reproduce. The data is fine. The sentence is fine. The distance between the sentence and the syntax is the whole problem.

Conditional aggregation — sum, count, or average the rows that qualify — is the second thing everyone learns to want from a spreadsheet, right after basic arithmetic. It is also where the formula language stops being intuitive, because Excel's criteria argument is a mini-language wearing a string's clothing: ">1000" is not a number, "<>West" is not a comparison anyone writes naturally, and the moment you need two conditions at once, SUMIF turns into SUMIFS and swaps its argument order on you.

This builder collapses the distance. You pick the function — SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF — fill in ranges and a criteria in plain fields, and get the formula with quoting, operators, and argument order handled correctly. Guardrails catch the classic mistakes as you type: trailing spaces, text where numbers belong, ranges of mismatched size. The output is a formula you can paste with the same confidence as typing it yourself would have cost you three attempts.

The tool models the question the way you already phrase it: which column do I test, what am I testing for, and which column do I aggregate? A field for the test range, a field for the criteria, a field for the sum range. Add another condition pair and the function selector flips from SUMIF to SUMIFS automatically, argument order and all. Pick COUNTIF and the sum-range field disappears, because counting needs nothing aggregated. The interface keeps the grammar honest so the formula can be honest.

The criteria field is where builders usually stop and where this one keeps going. Type a bare number and it's treated as equality. Type >3000 and the builder quotes the operator into the string properly. Type a cell reference in the value box and the concatenation form — ">"&G1 — is assembled for you, the pattern that everyone googles the first time they need a threshold that isn't hardcoded. Wildcards get a helper panel showing what * and ? will do to the specific text you entered, so "W*" stops being a guess about behavior.

Below the fields, the generated formula renders live with a plain-English recap underneath: "Sums column E where column A equals West." That recap line is not decoration. It catches reversed arguments — the moment the recap says "sums column A where column E equals West," you've found the bug that would otherwise surface as a mysteriously small total. Same engine, incidentally, that powers the group-by features in our SQL on CSV tool: criteria fields in, correct aggregation out.

1. 1

   Choose the function

   SUMIF for one condition; SUMIFS for several; the COUNTIF family when the answer is a count, not a total. The selector lists all five supported functions with a one-line description of when each applies. Switching later preserves your filled fields where they still make sense.

2. 2

   Enter the ranges

   Criteria range and sum range as A1-style references. The builder validates that both cover the same number of rows — mismatched sizes are the #VALUE! error waiting to happen, flagged here before it ever happens.

3. 3

   Fill the criteria

   A value or a cell reference, plus an optional comparison operator. Equal, not-equal, greater-than, less-than, and the inclusive variants — quoted automatically in the generated formula.

4. 4

   Add condition pairs if needed

   Each added pair switches the output to the plural form with correct argument order. Multiple pairs are AND logic, which the recap sentence states explicitly.

5. 5

   Read the recap, copy the formula

   The plain-English line beneath the preview confirms what will be computed. Copy with one click — quoting, separators, and parentheses are already correct.

Excel's criteria argument accepts several dialects inside one string, and fluency in them is the difference between writing the formula you want and writing one that almost does something else. Here is the full working vocabulary.

Three of these deserve their own warning labels. First, wildcards are active by default in text criteria, so a criteria of "*" on a column of part numbers will happily match everything textual — and if your part numbers contain real asterisks, you need the tilde escape or the count will be wrong forever. Second, comparison operators like > work only against numbers and dates; text cells are simply skipped by numeric comparisons rather than erroring, which converts a data problem into a quietly too-small total. Third, the blank-matchers are inverted from every instinct: "<>" means has content, "=" means blank. People guess the opposite and spend an afternoon wondering why the counts won't reconcile.

-- Cell-reference thresholds: operator quoted, reference concatenated
=SUMIF(E2:E7, ">"&G1)          ' sum revenues above the value in G1

-- Date windows: two pairs, one date column
=SUMIFS(E2:E7, A2:A7, ">="&DATE(2026,1,1), A2:A7, "<"&DATE(2027,1,1))

-- Not-equal exclusion
=SUMIF(B2:B7, "<>West", E2:E7)   ' everything except the West rows

The two functions were born a decade apart — SUMIF and COUNTIF descend from Excel 5.0-era worksheet functions, while the plural forms arrived with Excel 2007 — and their signatures show the seam. SUMIF puts the sum range last and optional: SUMIF(range, criteria, [sum_range]), where omitting the third argument sums the tested range itself. SUMIFS leads with the sum range: SUMIFS(sum_range, criteria_range1, criteria1, ...), no option, up to 127 pairs. COUNTIF takes just a range and a criteria; COUNTIFS takes pairs. AVERAGEIF mirrors SUMIF; AVERAGEIFS mirrors SUMIFS.

The order reversal is not a design decision anyone would defend. It survives because changing either signature now would break a billion existing workbooks, so instead every Excel course since 2007 has taught "note the order flip" as a rite of passage, and every year a new cohort pastes SUMIF argument layouts into SUMIFS and computes the sum of a region column. The builder makes the issue invisible: you fill in ranges by role — what to test, what to aggregate — and the compiler places them in the correct slot for whichever function the condition count requires. Switching from one condition to two rewrites the formula's structure automatically. The recap sentence is the audit trail.

Here is a six-row ledger, small enough to verify by hand. Region, rep, product, units, revenue — the shape of every sales export since spreadsheets began. Every formula below was generated from this data by the builder, and every total is checkable with mental arithmetic.

     A      B         C        D     E
1  Region  Rep       Product  Units Revenue
2  West    A. Chen   Widget   120   4980.00
3  East    J. Marsh  Widget    90   3735.00
4  West    A. Chen   Gadget    40   3400.00
5  East    P. Okafor Widget    60   2490.00
6  West    R. Silva  Gizmo     75   5625.00
7  South   P. Okafor Gadget    25   2125.00

• Numbers stored as text are invisible to numeric criteria. A revenue column pasted from a report can look numeric and sum to zero, because SUMIF skips text-typed cells in the sum range. The builder warns when the sum-range field looks like a label row; the fix in the workbook is converting the column — multiply by 1 via paste-special, or use Data → Text to Columns.
• Trailing spaces defeat equality. "West " with a space is not West, and the count comes out one row short. These spaces are invisible in the cell, visible in the formula bar, and the leading cause of criteria that "should work." The builder trims and flags them in the criteria field.
• Wildcards strike unintentionally. If SKU values literally contain * or ?, a plain equality criteria interprets them as wildcards and matches too much. Escape with tilde: "~*" means an actual asterisk. Nobody has ever enjoyed learning this.
• Whole-column references are safe but slow at scale. SUMIF over A:A works fine — the function uses used-range optimization — but a 40-conditional-pair dashboard over a million rows is a recalculation you can watch. Bounded ranges (A2:A5000) keep volatile workbooks honest.
• Dates as text criteria misfire. "">3/31/2026"" compares a number against a text rendering — sometimes accidentally right, structurally wrong. Use the concatenation with DATE(): ">"&DATE(2026,3,31), which compares date serials to date serials.
• COUNTIF criteria longer than 255 characters error out with #VALUE!. It sounds absurd until someone embeds a multi-clause OR string in one criteria. Split the logic into COUNTIFS pairs or use SUMPRODUCT.
• Merged cells break the geometry. A criteria range containing merged cells reports the value only in the anchor cell and blanks elsewhere, so per-region totals undercount every merged region label. Unmerge before aggregating, or the numbers will look plausible and be wrong.
• The 127-pair ceiling. SUMIFS accepts up to 127 range/criteria pairs, which sounds unlimited until a multi-select report UI generates criteria dynamically. If you're near triple digits, a PivotTable or a helper flag column is the maintainable answer.

Sales ops run on this function family, full stop. Commission math is SUMIFS (rep, period, minimum threshold); pipeline coverage is COUNTIFS over stage and close date; territory rollups are SUMIF on region when the CRM export lands in a sheet. The person doing this work knows exactly what they want and types criteria a hundred times a week — for them, the builder is speed and a guardrail against the trailing-space class of error that costs an hour per quarter.

Finance teams meet the same functions in reconciliation clothing: sum the GL lines matching this account and cost center, count the exceptions between two exports, average the settlement amounts in a date window. The criteria are more precise, the tolerance for a wrong total lower, and the audit-trail appeal of generating formulas from stated fields — instead of hand-editing a stranger's formula — is real. A recap sentence that says what the formula does is one more line of review documentation they didn't have to write.

The third group is teachers and small-business owners, for whom "total spend by category" or "count of students above grade level" is the entire analytics requirement. They don't want to learn the criteria mini-language; they want the sentence they're thinking to become a cell that computes. That's the same audience the IF Formula Builder serves from the branching side, and the two tools together cover most of what a spreadsheet civilian ever needs a formula for.

Three roads to "total revenue for the West region," and the choice is genuinely situational:

The working rule we recommend: use a PivotTable while the question is still exploratory — while you're asking "what even is the shape of this data" — and graduate to SUMIFS the moment the numbers land in something with a deadline on it. A formula is a commitment: it recalculates, it documents its own logic, and it survives a colleague opening the file. The FILTER route is what a modern-shop version of that commitment looks like, with the caveat that anyone still on Excel 2019 sees a #NAME? where your elegant dynamic array used to be. For lookup-shaped variants of the same reporting problem, the VLOOKUP Formula Generator is the neighboring tool.

Nothing you type into the builder goes anywhere. The ranges, the criteria, the generated formula, the recap — all of it exists in your browser tab and nowhere else. No account, no history, no telemetry about which columns your company aggregates. That matters for the same reason every formula tool on this site makes the same promise: the fields people fill in describe their business — revenue by region, headcount by department — and the correct number of parties with access to that description is one.

The builder also runs entirely offline once loaded, since it's plain client-side JavaScript. Build commission formulas from a plane, verify counts from a plant floor, close the tab when done. The formula you paste into your workbook is the only artifact, and it lives where you put it.

When conditions multiply into full branching logic, the IF Formula Builder handles the tree. When aggregation turns into retrieval — "bring back the price for this SKU" — the VLOOKUP Formula Generator writes the lookup. And when a generated SUMIFS needs verifying against the business rule it encodes, Excel Formula Explainer reads it back in plain English, clause by clause.