2

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?

1

Assuming 4 is in D2, please try:

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

in B5 and copy it across to suit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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