I am creating a budget template and have a formula that multiplies cells. However, not every cell will have a number in it. How do I make it so the formula will ignore blank cells or make them = 1 instead of = 0?

(Example: formula is =B1*B2*B3. In some rows, B3 is empty, in other it has a value. In other rows, B2 is empty, but B1 and B3 have values. How do I create a formula that ignores or counts empty cells as 1 to make the formula work across the board?)

2 Answers 2


I think you can use the Ifs statement

=IFS(B1="",1*B2*B3; B2="", B1*1*B3; B3="", B1*B2*1)

I hope this will help


Given you are multiplying another solution would be to replace the empty cells with 1, provided that wouldn't impact in other aspects

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.