Excel PRODUCT Function — Multiply a Whole Range (and Why It's Not Just *)

TL;DR — PRODUCT multiplies everything you hand it into a single number: =PRODUCT(A1:A5) is A1×A2×A3×A4×A5. It's SUM's multiplication twin. The reason to prefer it over =A1*A2*A3*A4*A5 isn't shorter typing — it's that PRODUCT ignores text and blank cells, while * errors on text and turns your whole product into 0 the moment one cell is empty. Reach for it to multiply a range, to compound growth rates with =PRODUCT(1+range), and anywhere a stray blank would otherwise wipe out a manual * chain.

=PRODUCT(A1:A5)        ' multiply every number in the range -> one value
=PRODUCT(2, 3, 4)      ' arguments work too -> 24
=PRODUCT(A1:A5)*1.2    ' mix a range with a scalar -> product, then +20%
=PRODUCT(1+B2:B13)-1   ' compound 12 monthly growth rates into a total return

Search "PRODUCT function Excel" and most pages show you =PRODUCT(number1, number2, …) and move on, as if the only thing worth knowing is that it multiplies. But if multiplying were the whole story you'd just type * and never think about it. The reason PRODUCT exists — and the reason it occasionally saves a model from a silent, wrong 0 — is how it treats the messy things a real range contains: text you didn't expect, and cells that happen to be empty. That's what this page leads with.

What you'll learn

The mental model: PRODUCT is to × what SUM is to +
Why a single blank cell makes =A1*A2*A3 return 0, but PRODUCT doesn't
How PRODUCT skips text in a range instead of throwing #VALUE!
The compounding trick: =PRODUCT(1+range) for cumulative growth
Why PRODUCT is not SUMPRODUCT, and when you want each
The judgment call: when a plain chain of * is genuinely the better tool

The mental model: PRODUCT is SUM's multiplication twin

You already trust SUM to add up a column without writing A1+A2+A3+…. PRODUCT is the exact same idea for multiplication: point it at a range (or a list of numbers) and it multiplies them all into one result. Everything you know about SUM — that it takes ranges, individual cells, constants, or a mix — transfers directly.

The place the twins diverge is in how they treat non-numbers, and that difference is the entire practical value of PRODUCT. SUM and PRODUCT both quietly ignore text and blanks inside a range. But the operators behind them behave very differently when a range isn't clean: + treats a blank as 0 (harmless — adding zero changes nothing), while × treats a blank as 0 too (catastrophic — multiplying by zero destroys the answer). Hold that asymmetry and the rest of this page follows.

The blank-cell trap: why `=A1A2A3` can return 0

Here is the failure mode that makes PRODUCT worth knowing. Say A1=5, A2=4, and A3 is empty (not zero — just never filled in):

=A1*A2*A3        ' -> 0        (empty cell coerced to 0, wipes the product)
=PRODUCT(A1:A3)  ' -> 20       (empty cell ignored -> 5 × 4)

In an arithmetic expression, an empty cell evaluates to 0. Multiply by that 0 and your carefully built product collapses to nothing — with no error to warn you. In a long * chain across a column that's still being filled in, one not-yet-entered value silently zeroes the whole thing. PRODUCT steps over blanks entirely, multiplying only the numbers that are actually there.

This is the same reason you reach for SUM instead of + across a range: not because it's shorter, but because it's robust to the gaps that real spreadsheets have. A model that multiplies a range should almost never do it with bare *.

Text in a range: skipped, not errored

The second thing PRODUCT handles gracefully is stray text. Suppose a range of quantities has a "N/A" typed into one cell:

=A1*A2*A3        ' -> #VALUE!   (× can't multiply by the text "N/A")
=PRODUCT(A1:A3)  ' -> 20        (text in the range is ignored)

When PRODUCT scans a range or array, it counts only the numbers — text, logical values (TRUE/FALSE), and blanks are skipped. The * operator has no such grace; the instant it hits text it can't convert, it returns #VALUE! and your formula is dead.

One sharp edge worth knowing: this leniency applies to text sitting inside a referenced range. A text value passed as a direct argument that can't be converted to a number — like =PRODUCT(2, "cat", 3) — still errors, because you've handed PRODUCT something it was told to treat as a number. Text that looks numeric ("6") is converted and multiplied. The rule: text in a range is ignored; unconvertible text as a literal argument is an error.

The compounding trick: =PRODUCT(1+range)

The single most useful real-world use of PRODUCT isn't multiplying quantities — it's compounding growth. If you have twelve monthly returns (say 2%, -1%, 3%, …) in B2:B13, the total return for the year isn't their sum; it's what you get by chaining (1 + each) together:

=PRODUCT(1+B2:B13)-1     ' compound monthly returns into an annual total

1+B2:B13 turns each return into a growth factor (1.02, 0.99, 1.03, …), PRODUCT multiplies them into the cumulative factor, and -1 converts back to a percentage. The same pattern compounds interest rates, price changes, survival rates, or any month-over-month multiplier. (In modern Excel this spills automatically; in older versions enter it with Ctrl+Shift+Enter as an array formula.) This one line replaces a helper column of running products and is the reason finance models lean on PRODUCT far more than on multiplying quantities.

PRODUCT is not SUMPRODUCT

Because the names look like cousins, people reach for the wrong one. They do genuinely different jobs:

=PRODUCT(A1:A3)           ' A1 × A2 × A3        -> one product of three cells
=SUMPRODUCT(A1:A3, B1:B3) ' A1×B1 + A2×B2 + A3×B3 -> pairwise, then summed

PRODUCT multiplies a single set of numbers into one value. SUMPRODUCT multiplies two (or more) ranges element by element and then adds the results — it's a weighted-total engine (quantity × price, summed), not a "multiply everything" engine. If you're computing a dot product, a weighted average, or a conditional count, you want SUMPRODUCT. If you literally want a × b × c, you want PRODUCT. See the SUMPRODUCT guide for the weighted-total pattern.

A conditional product

PRODUCT has no built-in IF the way SUMIFS does, but you can multiply only the values that meet a condition by feeding it an array where non-matching rows become 1 (the multiplicative identity, which leaves the product unchanged):

=PRODUCT(IF(A2:A10="West", B2:B10, 1))   ' multiply only the "West" rows

Every non-West row contributes a 1 and drops out; the West rows multiply together. It's the multiplicative sibling of SUMIF's "add only the matches" idea, with 1 playing the role that 0 plays in a conditional sum.

The judgment call: when plain * wins

PRODUCT's tolerance for blanks and text is a feature — until it hides a problem. If a cell in your range is supposed to always hold a number, a blank there is a data error you want to notice, and PRODUCT silently skipping it can mask a broken upstream formula. In that case, a plain =A1*A2*A3 that returns 0 or #VALUE! is doing you a favor by failing loudly.

So the honest rule is: use PRODUCT when the range legitimately contains gaps or mixed content you want stepped over; use * when every cell must be a number and you'd rather see the error. For two or three known cells, * also reads more clearly — =length*width*height says what it means better than =PRODUCT(A1:A3). PRODUCT earns its place on ranges, on compounding, and anywhere a stray blank would otherwise cost you the whole answer.

How ExcelMaster helps

A product that comes back as 0 or #VALUE! when you expected a real number is one of those bugs that looks like a mystery until you spot the blank or the stray text. Ask ExcelMaster "why is my multiplication formula returning zero?" and it traces the chain to the empty cell, and rewrites it as =PRODUCT(...) so the gap stops poisoning the result. Tell it "compound these twelve monthly returns into an annual figure" and it wires up =PRODUCT(1+B2:B13)-1 with the growth factors already in place.

Frequently asked questions

What does the PRODUCT function do in Excel?

PRODUCT multiplies all the numbers you give it and returns a single result. =PRODUCT(A1:A5) is the same as A1×A2×A3×A4×A5, and =PRODUCT(2,3,4) returns 24. It accepts ranges, individual cells, constants, or any mix — exactly like SUM, but multiplying instead of adding.

What's the difference between PRODUCT and the * operator?

They multiply the same way, but handle messy ranges differently. PRODUCT ignores text and blank cells inside a range; the * operator returns #VALUE! when it hits text, and treats an empty cell as 0 — which silently turns the whole product into 0. Use PRODUCT on ranges that may have gaps; use * when every cell must be a number and you want the error.

Why does my PRODUCT formula return 0?

Almost always because one of the cells actually contains 0 (not a blank — PRODUCT ignores blanks). Multiplying by zero makes the entire product zero. Scan the range for a genuine 0 value; if you meant to skip that cell, remove the zero or exclude the cell from the range.

Is PRODUCT the same as SUMPRODUCT?

No. PRODUCT multiplies one set of numbers into a single value. SUMPRODUCT multiplies two or more ranges element by element and then adds the results — it's for weighted totals like quantity × price, summed. Different jobs; see the SUMPRODUCT guide.

How do I compound growth rates or interest with PRODUCT?

Turn each rate into a growth factor by adding 1, multiply them all with PRODUCT, then subtract 1: =PRODUCT(1+B2:B13)-1. This compounds monthly returns, interest rates, or price changes into a cumulative figure — the correct way to combine period-over-period percentages (you can't just add them).

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-26.