Summing problem

[Dr. Weird]

Excel 2000, here's the prob:

Column
A B C
100 50
200 150
300 250 20
400 10
-------------------------
Total


Total for Col A is standard, SUM(A1:A4). Total for Col B must be calculated
like this: if there is empty cell in Col B (B1 and B4 in this example), it's
taken from the Col A, so Total for Col B is 100 (from Col A)+150+250+400
(again from Col A). The same applies to Col C (Total is 50+200(from Col
A)+20+10).

It's 2am now, and I'm going a bit crazy with IF, SUM, SUMIF, ISBLANK
megaformulas . Thought of a hidden column for calculation (one for each of
the columns maybe, there will be 5-6 max) but is there any other solution?

Thanks.

 

[Peo Sjoblom]

For B

=SUMPRODUCT((B1:B4="")*(A1:A4)+B1:B4)

for C

=SUMPRODUCT((C1:C4="")*(A1:A4)+C1:C4)

 

[Ron Rosenfeld]

Excel 2000, here's the prob:

Column
A B C
100 50
200 150
300 250 20
400 10
-------------------------
Total


Total for Col A is standard, SUM(A1:A4). Total for Col B must be calculated
like this: if there is empty cell in Col B (B1 and B4 in this example), it's
taken from the Col A, so Total for Col B is 100 (from Col A)+150+250+400
(again from Col A). The same applies to Col C (Total is 50+200(from Col
A)+20+10).

It's 2am now, and I'm going a bit crazy with IF, SUM, SUMIF, ISBLANK
megaformulas . Thought of a hidden column for calculation (one for each of
the columns maybe, there will be 5-6 max) but is there any other solution?

Thanks.

*Array-Enter* this formula in B6,then copy/drag it across as needed:

=SUM(IF(ISNUMBER(B1:B4),B1:B4,$A$1:$A$4))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron