The formula
Excel & Google SheetsExcelGoogle Sheets
=LET(subtotal,SUMPRODUCT(B2:B4,C2:C4),tax,subtotal*B6,subtotal+tax)In E2 → $81.00
Same result without LETExcelGoogle Sheets
=SUMPRODUCT(B2:B4,C2:C4)*(1+B6)In E3 → $81.00
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | ||
| 2 | Pens | 10 | 1.5 | $81.00 | |
| 3 | Paper | 4 | 6 | $81.00 | |
| 4 | Ink | 2 | 18 | ||
| 5 | |||||
| 6 | Tax rate | 8% |
How it works
- subtotal
- A name you choose (no spaces, can't look like a cell address such as A1).
- SUMPRODUCT(B2:B4,C2:C4)
- The value stored in subtotal: 15 + 24 + 36 = 75.
- tax,subtotal*B6
- A second name that reuses the first.
- subtotal+tax
- The last argument is the result: 75 + 6 = 81.
Common errors and fixes
Why does the formula return #NAME??
A name is misspelled in the calculation, or your Excel doesn't have LET (2019 and older).
Why does the formula return #VALUE!?
A name looks like a cell reference (e.g. "tax1" is fine, "AB12" is not).
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.