SUM a column of values that whenever a cell is empty gets it from a default column

Question

I am trying to sum a column of values but whenever a cell is empty then it sums the value from a default column. Example:

+---------+---------+---------+---------+ | Default | A | B | C | +---------+---------+---------+---------+ | 10 | | 0 | 4 | | 10 | | | 2 | | 10 | | 0 | | +---------+---------+---------+---------+ | TOTAL | 30 | 10 | 16 | +---------+---------+---------+---------+

• A: All cells are empty so it sums all cells from Default column 10 + 10 + 10 = 30
• B: First and last cell are not empty (it is '0') so it sums 0 + 10 + 0 = 10
• C: Last cell is empty so it grabs the value from Default column: 4 + 2 + 10 = 16

I've seen similar questions using FILTER (another) and Matrix multiplication but I'm just able to sum one or the other, not mix both columns. Should I be using a conditional IF? Can it be done with a formula?

Answer 1

Assuming 4 is in D2, please try:

=sum(B2:B4)+SUMIF(B2:B4,"",$A2:$A4)

in B5 and copy it across to suit.