Conditional sum

Question

I need a quick macro/formula. I have two columns:

          A   |   B
              |    
       |  10  |   9  |
       |  11  |      |
       |  8   |   10 |
       |     N/A     |
       |  4   |      |
       |  7   |   8  |

total  |  40  |   27 |

Now I want to add a sum to a column that adds up all the values in column A BUT replaces that value with the value in column B if it exists.

So I would expect the sum to end up being 9 + 11 + 10 + 4 + 8 = 42

I'm not entirely sure about the power of Excel's calculations or how to go about performing a for loop within a formula.

Answer 1

There are MANY ways to do this, but I would do it as two sums added together....

Assuming you had the data you supplied in your question in cells A1 to B6, your formula could look as follows:

=SUMIF(B1:B6,"",A1:A6)+SUM(B1:B6)

In essence, you're saying:

1. Sum A1:A6 anywhere B1:B6 is blank

PLUS

1. Sum B1:B6

Hope this makes sense and helps!