Excel formula product of multiple cells: the overlooked blank trap

If you need an Excel formula product of multiple cells, the PRODUCT function does it in one short formula. It multiplies every number you hand it and returns the result. Most guides stop at the syntax, but the real trouble starts when your range has blanks, text, or rows you want to skip.

You will get the exact formulas to copy, a side by side look at PRODUCT and the * operator, and a conditional version that multiplies only matching rows. Along the way you will see where each approach quietly breaks. That way you can pick the right one before you share the file.

What the PRODUCT function does

PRODUCT multiplies every number you give it and returns a single result. The syntax is PRODUCT(number1, [number2], ...), and only the first argument is required. Microsoft's PRODUCT function documentation says you can pass up to 255 arguments, and each one can be a number, a cell, or a range. Because a whole range counts as one argument, you will rarely hit that limit.

Say cells A2:A4 hold 5, 15 and 30. Multiplying them gives 2250, and adding a second argument of 2 doubles the answer to 4500, which matches Microsoft's own example. Here are the formulas to try.

=PRODUCT(A2:A4)
=PRODUCT(A2:A4, 2)
=A2*A3*A4

The first and third formulas both return 2250, while the second returns 4500. For three cells the * operator is just as short. PRODUCT wins on longer ranges because you write one range instead of typing every cell, and Microsoft notes that =PRODUCT(A1:A3, C1:C3) is the same as multiplying all six cells one by one.

PRODUCT versus the * operator when cells are blank

The two approaches match only when every cell holds a number. Once a blank or text value appears, they split. Exceljet's guide to the PRODUCT function explains that PRODUCT ignores empty cells and text, while the * operator treats a blank as zero and returns a #VALUE! error on text.

Choosing between them for an Excel formula product of multiple cells comes down to what a blank means in your sheet. If blank means "not applicable", PRODUCT gives the answer you want. If blank means "someone forgot to type a value", PRODUCT hides the gap and returns a number that looks fine. In that case the zero from the * operator is a better warning sign.

How to multiply only the rows that match a condition

The formula

Wrap an IF inside PRODUCT and return 1 for every row you want to skip. The number 1 is neutral in multiplication, so skipped rows leave the answer unchanged. Imagine labels in A2:A5 and values in B2:B5, and you want the product of only the rows labelled A.

=PRODUCT(IF(A2:A5="A",B2:B5,1))

Rows 2, 4 and 5 match, so the formula multiplies 2, 3 and 4 and returns 24. The row with the value 10 swaps to 1, so it has no effect on the result.

Entering it correctly

Because IF checks every row at once, Excel has to evaluate it as an array. In a MrExcel forum thread on conditional products, one reply suggests =PRODUCT(IF(A:A="A",B:B,1)) confirmed with Control-Shift-Enter. In older versions of Excel you do the same, and curly braces appear around the formula. In Google Sheets, wrap the whole formula in ARRAYFORMULA instead. Newer Microsoft 365 builds often calculate it with a plain Enter, but if you see one wrong value, switch to Ctrl+Shift+Enter.

A conditional Excel formula product of multiple cells follows the same pattern as the MIN IF and MAX IF formulas that Contextures covers for Excel 2016 and earlier. Use a bounded range such as A2:A5 instead of whole columns, since full columns pull in headers and thousands of extra rows to check. Also watch for blanks, because on a matching row an empty value cell can act as zero inside the array, which turns the whole product into 0.

Mistakes that give a wrong product without an error

The scary errors are the ones that never show up. PRODUCT rarely throws a warning, so a wrong answer looks just like a right one. Check these five spots first.

• Numbers stored as text: PRODUCT ignores text inside a range, so those cells vanish from the answer. Test a suspect cell with ISNUMBER.
• A stray zero: one 0 anywhere in the range makes the whole product 0, even if every other cell looks right.
• Blank cells: PRODUCT skips them, but the * operator returns 0. Know which behavior your sheet needs.
• Growth rates: multiplying 5% by 3% is not compounding. Add a helper column with =1+B2, then run PRODUCT on that column and subtract 1.
• Blanks on matching rows in a conditional product: they can act as zero and wipe out the result.

Letting an AI write the formula for you

If you would rather describe the result than type the syntax, Formula Foundry's Excel formula generator turns a plain sentence into a formula. A prompt like "multiply the values in column B only where column A equals A, and skip blanks" gives it the three details it needs: the condition, the range and the blank rule. Asking for an Excel formula product of multiple cells in plain words works best when you name the exact columns.

Whichever route you take, test the result on a small range you can check by hand. The 5, 15 and 30 data set should return 2250, and the labelled A rows above should return 24. If you want more everyday functions to try next, the beginner Excel formula list with examples is a good place to continue.

Action steps

1. Pick your range: Select the cells you want to multiply and write them as one range, for example =PRODUCT(A2:A4).
2. Decide what a blank means: Use PRODUCT if blanks mean not applicable. Use the * operator if a blank should warn you of missing data.
3. Check for text numbers: Run ISNUMBER on a few cells so values stored as text do not drop out of the product silently.
4. Add a condition if needed: Wrap IF inside PRODUCT and return 1 for skipped rows, then confirm with Ctrl+Shift+Enter in older Excel or use ARRAYFORMULA in Google Sheets.
5. Test with a known answer: Try a small range you can multiply by hand, such as 5, 15 and 30 giving 2250, before using the formula on real data.

Frequently asked questions

What is the Excel formula for the product of a range?

Use =PRODUCT(A2:A4), replacing the range with your own cells. It multiplies every number in the range and returns one result. Microsoft's documentation shows that this equals multiplying the cells with the * operator.

Does PRODUCT ignore blank cells and text?

Yes. When you pass a range or cell reference, PRODUCT ignores empty cells, logical values and text. The * operator behaves differently: it returns 0 for a blank and a #VALUE! error for text.

How many arguments can PRODUCT take?

Microsoft says PRODUCT accepts up to 255 arguments. Each argument can be a number, a cell or a range, so one range still counts as a single argument.

How do I multiply only cells that meet a condition?

Use =PRODUCT(IF(A2:A5="A",B2:B5,1)). The 1 keeps skipped rows from changing the result. In older Excel, confirm it with Ctrl+Shift+Enter, and in Google Sheets wrap it in ARRAYFORMULA.

Why does my PRODUCT formula return 0?

A zero in the range makes the whole product 0. Also check conditional formulas for blank value cells on matching rows, which can act as zero inside the array.