Excel blank cell reference to add cell next to it twice

Question

asked Jan 24, 2021 in Technique[技术] by 深蓝 (71.8m points)

Is there a way to sum a row of cells up, but if a cell in the row is blank then add the cell on the left side of the blank cell two times?

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

2.3k views

1 Answer

深蓝 · Answer 1 · 2021-01-23T23:09:05+0000

suppose your data is in A1:K1

Add any dummy text in cell next to it L1 (which can be a hidden column), thereafter add this formula in the cell next to it M1

=SUM(A1:K1, SUMPRODUCT((A1:K1)*ISBLANK(B1:L1)))

You'll get your results