 SOLVED

# Multiply cells but ignore zeros and blanks cells

How do I multiple numbers in different cells BUT ignore blanks and zeros. (Please not this a long excel sheet). I want to multiple all the numbers from cell A1:H1 and put the answer in cell J in total. The answer in the first cell should be 80 (1*2*5*2*2*2).

However because I have zeros randomly through the work book a simple ** would not work.

I want a statement that reads, multiply cell however if zero or blank ignore.

 total 1 0 2 5 2 0 2 2 1 0 2 1 2 0 2 2 1 0 1 8 2 0 2 2 1 0 1 4 2 0 2 2 1 0 1 1 2 0 2 2 1 0 7 2 0 2 2 1 0 4 2 0 2 2 9 3 0 2 0 2 2 9 2 7 2 0 2 2 9 2 3 2 0 2 2
4 Replies
best response confirmed by Excelstudent (New Contributor)
Solution

# Re: Multiply cells but ignore zeros and blanks cells

@Excelstudent If you are on a recent Excel version or on-line, this will work. Or perhaps better to use:

``=PRODUCT(FILTER(A2:H2,A2:H2<>0))``

Then you capture everything except zeros and blanks.

# Re: Multiply cells but ignore zeros and blanks cells

``=PRODUCT(IF((A1:H1<>0)*(A1:H1<>""),A1:H1))``

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. # Re: Multiply cells but ignore zeros and blanks cells

Using 365, I automatically go for a single dynamic range formula for the entire table.  The key is to replace zeros and blanks with 1 which is the identity under multiplication.

``````= BYROW(IF(data, data, 1),
LAMBDA(row, PRODUCT(row))
)``````

If the Lambda function is named, this simplifies to

``= BYROW(IF(data,data,1), Productλ)``

... all of which looks nothing like Excel I realise!

# Re: Multiply cells but ignore zeros and blanks cells

Thank you very much for your help. This worked wonderfully.