Invoice Total with Tax (Line Items)

All versionsSUMPRODUCT

Build an invoice that totals itself. SUMPRODUCT multiplies quantity by price across every line in one shot, then a tax line and a grand total finish it — no helper column of line totals needed.

Quick formula: for quantities in B2:B10 and unit prices in C2:C10:

=SUMPRODUCT(B2:B10, C2:C10)

That’s the subtotal. Add tax with =subtotal * taxRate and a grand total with =subtotal + tax.

Functions used (tap for the full reference guide):

SUMPRODUCT ROUND

The example

Three line items, an 8.25% tax, and the grand total.

	A	B	C
1	Item	Qty	Price
2	Widget	3	$12.00
3	Gadget	2	$25.00
4	Subtotal		$86.00
5	Tax 8.25%		$7.10
6	Total		$93.10

The formula

Subtotal, tax, and total:

=SUMPRODUCT(B2:B3, C2:C3) // subtotal 86.00 =ROUND(subtotal * 0.0825, 2) // tax 7.10 =subtotal + tax // total 93.10

How it works

SUMPRODUCT does the line math and the sum together:

SUMPRODUCT multiplies each quantity by its price row by row, then adds the products — the subtotal in one cell.
No need for a “line total” column unless you want to show it; the formula handles it internally.
Compute tax on the subtotal and ROUND to 2 decimals so cents don’t drift: =ROUND(subtotal*rate, 2).
The grand total is simply subtotal plus tax.

Tax only some items? Add a taxable flag column (1/0) and multiply it in: =SUMPRODUCT(B2:B10, C2:C10, D2:D10)*rate taxes only the flagged lines. For a discount, subtract a discount column the same way.

Try it: interactive demo

Live demo

Enter lines as “qty,price”; set the tax rate.

Tax %

Subtotal · Tax · Total

Variations

Tax only flagged items

1/0 taxable column D:

=SUMPRODUCT(B2:B10, C2:C10, D2:D10) * rate

Apply a discount

Subtract a discount before tax:

=SUMPRODUCT(B2:B10, C2:C10) * (1 - discount)

Show line totals too

Per-row column:

=B2 * C2

Pitfalls & errors

Round the tax. Without ROUND(…,2), fractions of a cent accumulate and the printed total can be a penny off.

Equal-length ranges. The qty and price ranges must be the same size, or SUMPRODUCT returns #VALUE!.

Blanks are fine, text isn’t. Empty cells multiply as 0, but text in a number column breaks the product. Keep qty/price columns numeric.

Practice workbook

📊

Download the free Invoice Total with Tax (Line Items) practice workbook

A working invoice with SUMPRODUCT subtotal, tax, total, plus the taxable-flag and discount variants and 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I total an invoice with tax in Excel?

Use =SUMPRODUCT(qty_range, price_range) for the subtotal, then =ROUND(subtotal*taxRate,2) for tax and subtotal+tax for the grand total.

How do I tax only some line items?

Add a 1/0 taxable column and include it: =SUMPRODUCT(qty, price, taxable_flag)*rate taxes only the flagged lines.

Why is my invoice total a penny off?

Unrounded tax carries fractional cents. Wrap the tax in ROUND(...,2) so it matches the printed figures.

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

Function references: SUMPRODUCT · ROUND