Forum Discussion
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 |
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.
4 Replies
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!
=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.
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.
- Thank you very much for your help. This worked wonderfully.
