sumup blank cell to blank cell

[kingkingking]

Can any body think of a formula if i want to sum up each collection as
follow, because i have a lot of collection cell i have to calculate.
A 5000
B 2000
C 3000
Collection : 8000

D 1000
E 2000
F 4000
Collection 7000

G
H
I
Collection

 

[Bob Phillips]

B2 and copy down

=IF(A2<>"","",SUM(A2:INDEX($A$1:A1,MAX(1,MAX(IF($A$1:A1="",ROW($A$1:A1)))))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernard Liengme]

Please explain the 8000 (5+2+3=11)
Also what cells are the 'collections' in ?

best wishes

 

[kingkingking]

sorry for wrong calculation

Please explain the 8000 (5+2+3=11)
Also what cells are the 'collections' in ?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[kingkingking]

Thanks you very much
i solve the problems
and it save me a lot of time
can you tell me where i can learn kind of thing like this so next time i
know what to do when i encounter something like this.

 

[Bob Phillips]

No, not really. It is just a question of trying, watching these groups and
learning.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)