How do I conditionally sum cells in excel?

问题:

Here is a table that I'm working with (an ascii art representation fo it):

|---|---------|-----------|--
|   |A        |B          |
|---|---------------------|--
|1  |price    |override   |
|---|---------|-----------|--
|2  |10       |           |
|---|---------|-----------|--
|3  |20       |100        |
|---|---------|-----------|--
|4  |30       |           |
|---|---------|-----------|--
|5  |40       |90         |
|---|---------|-----------|--
|6  |50       |           |
|---|---------|-----------|--
|7  |150      |280        | <-- SUMS
|---|---------|-----------|--
|   |         |           |

column A has some prices, column B has an override price, if any. Rows 1 to 6 have the prices, and row 7 has sums.

Row 7 is calculated as "=SUM(A1:A7)"

I want to write a formula for cell B7 that will do the following:

for every cell in range A1:A7
    if the override field for that price is blank
        add the original price to the total
    else
        add the override price to the total

My question: what formula will do the above?

I've looked at IF (cant figure out how to apply it on a range) and SUMIF(can't figure out how to get the "current cell" and "relative cell"), but I can't think of a way to do this. I'm a total novice at excel formulae.

回答1:

How about trying an array formula? In cell B7, try adding this:

=SUM(IF(ISBLANK(B2:B7), A2:A7, B2:B7))

And then pressing Ctrl+Shift+Enter to enter as an array formula. This will check if there is a value in column B and use it in the sum; otherwise, it will use the value in column A.

回答2:

It seems like the easiest is to create the third column, which cells you populate either with price or override by =IF(ISBLANK(B1); A1; B1) formula. Then you mouse drag the formula onto all cells (will update it accordingly). And then you just sum values in your third column. Sorry, not an excel artist

回答3:

I would have a new row.row C to collect the price needed to sum.

row C is like this

if(B1 = "",A1,B1)

you will have row C

C

10
100
30
90
50

then sum up this row C.you'll get what you want.