Raw produce is sales-tax exempt in most states; prepared food, crafts and value-added goods usually are not. A vendor selling across all three needs to total tax only on the taxable rows, and SUMPRODUCT does that in a single formula — multiplying each row's sales by 1 if taxable and 0 if exempt, then by the tax rate, and summing the result without a helper column.
Only the taxable rows (baked goods and crafts) contribute; produce and honey, both exempt, contribute zero.
The example
One Saturday's sales across four categories at an 8.25% local rate. Two categories are tax-exempt.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Taxable? | Sales | Rate | Tax due |
| 2 | Produce | No | 450 | 8.25% | $0.00 |
| 3 | Baked goods | Yes | 300 | 8.25% | $24.75 |
| 4 | Crafts | Yes | 220 | 8.25% | $18.15 |
| 5 | Honey | No | 150 | 8.25% | $0.00 |
The formula
The comparison inside SUMPRODUCT does the filtering, no IF needed:
How it works
Three arrays multiply together element by element, then sum:
(B2:B5="Yes")compares each cell in the range to the text "Yes" and produces an array of TRUE/FALSE, which Excel treats as 1/0 the moment it is multiplied.*C2:C5multiplies each row's sales by its 1-or-0 taxable flag, zeroing out the exempt rows (produce, honey) while leaving taxable rows (baked goods, crafts) unchanged.*D2:D5applies the tax rate to what is left, row by row.- SUMPRODUCT adds every element of the resulting array together in one step, without needing an array-entered formula or a helper column to hold the intermediate taxable-sales figures.
Add a SUM of just the tax-due column as a cross-check — if it does not match the SUMPRODUCT total, a rate or a Yes/No flag was typed wrong somewhere.
Try it: interactive demo
Enter sales for a taxable and an exempt category, and the tax rate.
Variations
Same total with SUMIF, no array math
If every taxable row shares one rate, SUMIF is simpler than SUMPRODUCT — sum just the taxable sales, then multiply by the rate once at the end.
Different rate per category
Drop the shared-rate assumption and multiply each row by its own rate column directly — SUMPRODUCT already supports this exactly as written above.
Pitfalls & errors
SUMPRODUCT ranges must all be the same size (same number of rows). Mixing B2:B5 with C2:C6 by one extra row silently misaligns the multiplication and produces a wrong, not obviously wrong, total.
Typing "yes" in lowercase in one row and "Yes" in another is not a problem — Excel's = comparison is not case-sensitive — but a trailing space ("Yes ") IS a mismatch and will silently zero out that row. TRIM the taxable column if it is typed by hand.
Keep the taxable/exempt flag as its own column rather than folding the logic into the tax rate (for example, entering 0% for exempt rows). A dedicated Yes/No column is auditable at a glance; a zeroed-out rate looks like a data-entry mistake.
Practice workbook
Frequently asked questions
Does SUMPRODUCT need to be entered with Ctrl+Shift+Enter like an array formula?
What if a state taxes some prepared food but not others?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class