Excel Formulas to Multiply Only Positive Numbers

Managing complex financial models or forecasting sheets in Excel often becomes frustrating when negative values skew your multiplication results. While standard operators like asterisk multiplication or the basic PRODUCT function are common baseline tools, they lack the native intelligence to filter out negative outliers.

Implementing conditional logic adds immense value, ensuring only constructive, positive growth figures impact your final metrics. Under this stipulation, any non-positive input is bypassed-treated as a neutral multiplier of one-to preserve calculation integrity. For example, using =IF(A2>0, A2, 1) * IF(B2>0, B2, 1) dynamically isolates positive drivers. Below, we outline the exact steps to configure this formula for your datasets.

In spreadsheet modeling, we often need to perform conditional calculations. While summing numbers based on criteria is straightforward with functions like SUMIF or SUMIFS, multiplying numbers conditionally is slightly more complex. Excel does not feature a native "PRODUCTIF" function. Therefore, if you need to multiply a series of numbers while ignoring negative values or zeros, you must combine several functions to achieve the desired outcome.

This guide explores the most effective formulas to multiply numbers only if they are positive. We will cover solutions compatible with modern versions of Excel (Microsoft 365 and Excel 2021) as well as legacy methods for older versions.

The Challenge of Conditional Multiplication

Before diving into the formulas, it is important to understand why conditional multiplication requires a unique approach. In addition, adding zero to a sum has no impact: 5 + 0 = 5. However, in multiplication, multiplying by zero wipes out your entire calculation: 5 * 0 = 0. Similarly, multiplying by a negative number reverses the sign of your product.

To multiply only positive numbers, our formulas must do one of two things:

• Filter out the non-positive numbers (negatives and zeros) entirely from the array before calculating the product.
• Substitute the non-positive numbers with the number 1, which is the multiplicative identity (any number multiplied by 1 remains unchanged).

Method 1: Using the FILTER and PRODUCT Functions (Excel 365 & 2021)

If you are using a modern version of Excel that supports dynamic arrays, the most elegant and intuitive solution is to combine the PRODUCT function with the FILTER function.

The Formula

=PRODUCT(FILTER(A2:A10, A2:A10 > 0))

How It Works

1. FILTER(A2:A10, A2:A10 > 0): This part of the formula looks at the range A2:A10 and extracts only the values that are strictly greater than zero. It discards any negative numbers, zeros, and text strings.
2. PRODUCT(...): This function takes the filtered array of positive numbers and multiplies them together.

Handling Edge Cases: What if there are no positive numbers?

If your range contains only negative numbers or zeros, the FILTER function will return a #CALC! error because there are no matching values. To prevent this, you can use the third argument of the FILTER function to return 1 (since multiplying by 1 does not change a product) or wrap the whole formula in IFERROR.

=PRODUCT(FILTER(A2:A10, A2:A10 > 0, 1))

If no positive numbers exist, the filter returns 1, and the PRODUCT function returns 1. Alternatively, if you prefer to show a blank space or a custom message when no positives are present, use IFERROR:

=IFERROR(PRODUCT(FILTER(A2:A10, A2:A10 > 0)), "No positive numbers")

Method 2: Using PRODUCT and IF (The Classic Array Formula)

For users running older versions of Excel (such as Excel 2019, 2016, or 2013), the FILTER function is not available. In this case, you must use a traditional array formula combining PRODUCT and IF.

The Formula

=PRODUCT(IF(A2:A10 > 0, A2:A10, ""))

How to Enter This Formula

Because this is an array formula in legacy Excel versions, typing the formula and pressing Enter will not work correctly (it will return a #VALUE! error or an incorrect result). Instead, you must:

1. Type the formula into the cell.
2. Press Ctrl + Shift + Enter on your keyboard.
3. Excel will automatically wrap your formula in curly braces like this: {=PRODUCT(IF(A2:A10 > 0, A2:A10, ""))}. Do not type these braces manually.

How It Works

• The logical test A2:A10 > 0 evaluates each cell in the range. It returns an array of TRUE and FALSE values.
• The IF function processes this array. If a cell is greater than zero, it returns the actual number. If it is not (meaning it is zero or negative), it returns an empty string ("").
• This results in an array that looks something like this: {5, "", 12, "", "", 8}.
• The PRODUCT function is designed to automatically ignore empty text strings (""). It only multiplies the remaining numerical values: 5 * 12 * 8 = 480.

Method 3: Multiplying Two Specific Cells (Row-by-Row Conditional Multiplication)

Sometimes, you do not want to multiply an entire vertical range. Instead, you might want to multiply two cells side-by-side (e.g., A2 and B2) on a row-by-row basis, but only if both values are positive. If one or both are negative, you might want to return 0, a blank, or only the value of the positive cell.

Case A: Return Blank/Zero if either cell is not positive

If your goal is to calculate a product only when both components are positive (such as calculating profit margins where both revenue and cost must be positive), use the AND function within an IF statement:

=IF(AND(A2 > 0, B2 > 0), A2 * B2, "")

This formula checks if both A2 and B2 are greater than zero. If true, it returns their product. If either is negative or zero, it returns a blank cell.

Case B: Multiply only the positive cells (skipping the non-positives)

If you want to multiply the cells but ignore any cell that is negative or zero (treating it as 1 so it doesn't affect the multiplication), use the following formula:

=IF(A2 > 0, A2, 1) * IF(B2 > 0, B2, 1)

With this logic:

• If A2 = 5 and B2 = -3, the calculation becomes 5 * 1, resulting in 5.
• If both are negative, the calculation becomes 1 * 1, resulting in 1 (which you can wrap in a secondary check if you want to display 0 instead).

Practical Example and Comparison

Let's look at a sample dataset to see how these formulas perform under different scenarios.

In this example, the formula ignores -5 and 0. The calculated product is 10 * 3 * 20 = 600.

Alternative: Multiplying Non-Zero Numbers (Including Negatives)

Sometimes, your requirement isn't strictly to multiply "positive" numbers, but rather to multiply all non-zero numbers (including negative numbers). If you want to keep negative numbers in your product but exclude zeros, you can adjust the logical operators slightly.

Using the modern FILTER approach:

=PRODUCT(FILTER(A2:A10, A2:A10 <> 0))

Using the legacy IF array formula:

=PRODUCT(IF(A2:A10 <> 0, A2:A10, ""))

(Remember to press Ctrl + Shift + Enter for the legacy version).

Summary: Which Method Should You Choose?

• Use Method 1 (PRODUCT + FILTER) if you and your team are using Microsoft 365 or Excel 2021. It is the easiest to read, write, and maintain, and it handles array processing natively without special keyboard shortcuts.
• Use Method 2 (PRODUCT + IF as an array formula) if you must maintain compatibility with older desktop installations of Excel (Excel 2019 and earlier).
• Use Method 3 (Simple IF checks) when you are dealing with a fixed, small set of individual cells (like multiplying adjacent columns) rather than a continuous vertical range.