Summing problem

  • Thread starter Thread starter Dr. Weird
  • Start date Start date
D

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.
 
For B

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

for C

=SUMPRODUCT((C1:C4="")*(A1:A4)+C1:C4)
 
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.
Click to expand...

*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
 
Back
Top