sumup blank cell to blank cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kingkingking - 30 Mar 2008 16:43 GMT

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

Reply to this Message

Bob Phillips - 30 Mar 2008 17:11 GMT

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.

Signature

---
HTH

Bob

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

> 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.
[quoted text clipped - 12 lines]
> I
> Collection

Reply to this Message

kingkingking - 30 Mar 2008 18:06 GMT

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" <bob.NGs@somewhere.com> ¼¶¼g©ó¶l¥ó·s»D:ubTONDokIHA.4480@TK2MSFTNGP03.phx.gbl...

> B2 and copy down
>
[quoted text clipped - 25 lines]
>> I
>> Collection

Reply to this Message

Bob Phillips - 30 Mar 2008 18:42 GMT

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

Signature

---
HTH

Bob

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

> Thanks you very much
> i solve the problems
[quoted text clipped - 32 lines]
>>> I
>>> Collection

Reply to this Message

Bernard Liengme - 30 Mar 2008 17:13 GMT

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

best wishes

Signature

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

> 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.
[quoted text clipped - 12 lines]
> I
> Collection

Reply to this Message

kingkingking - 30 Mar 2008 17:58 GMT

sorry for wrong calculation
"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> ¼¶¼g©ó¶l¥ó·s»D:%234bN5DokIHA.484@TK2MSFTNGP04.phx.gbl...

> Please explain the 8000 (5+2+3=11)
> Also what cells are the 'collections' in ?
[quoted text clipped - 16 lines]
>> I
>> Collection

Reply to this Message

Rate this thread: