How to code it in excel?

[Guest]

Could anyone please give me any suggestion on how to code following condition
in excel?

In colume A, it displays the date for activities.

In colume B, it displays a list of activities for everyday, such as input
products, output products and keep inventories. The number of activities is
not fixed, one day could performance 6 activities, but on the other days, it
could performance 9 activities, but a blank row must be inserted between two
days for separation.
the activities of "keep inventories" will only be performed once at the end
for each day, which will sum up all the quantity of products for today only.

In colume C, it displays the number of quantity for products.

Does anyone know how to code the condition in excel to sum up all the number
of quantity from above until it meets a blank row? please see below example
for detail description.

On 25 Sep, 2006, it contains a lists of activities as show below

[row 1] [a blank row was inserted]
[row 2] 25 Sep input products 10
[row 3] 25 Sep output products -5
[row 4] 25 Sep input products 7
[row 5] 25 Sep input products 6
[row 6] 25 Sep output products -15
[row 7] 25 Sep keep inventories 3 = 10-5+7+6-15, which sum up the available
inventories for today only.
[row 8] [a blank row was inserted]
[row 9] 26 Sep input products 20
[row 10] 26 Sep output products -15
[row 11] 26 Sep input products 8
[row 12] 26 Sep input products 3
[row 10] 26 Sep output products -10
[row 11] 26 Sep input products 6
[row 10] 26 Sep output products -5
[row 11] 26 Sep input products 4
[row 13] 26 Sep keep inventories 11 = 20-15+8+3-10+6-5+4, which sum up the
available inventories for today only.
[row 14] [a blank row was inserted]

Does anyone know how to code this situation in excel?
Thank you for any suggestion
Eric

 

[Guest]

Try entering this in D7 (obviously you cannot enter it in column C or it will
screw up the blank row between your data). It is an array formula, so you
must enter it using Cntrl+Shift+Enter or you'll get #VALUE!

=SUM(INDEX(C$1:C6,MAX(ISBLANK(C$1:C6)*ROW(C$1:C6)),1):C6)

Then copy to D13, etc.

 

[Guest]

Thank you for your suggestion

The formula solves part of the problem, it sums up all the value from above.
I would like to skip any value above the blank cell, such as
For the row 17, the sum of value would skip the value from row 1 to 8, and
only sum up the value from row 9 to 16.

Do you have any suggestion?
Thank you very much for your help
Eric

[row 1] [a blank row was inserted]
[row 2] 25 Sep input products 10
[row 3] 25 Sep output products -5
[row 4] 25 Sep input products 7
[row 5] 25 Sep input products 6
[row 6] 25 Sep output products -15
[row 7] 25 Sep keep inventories 3 = 10-5+7+6-15, which sum up the available
inventories for today only.
[row 8] [a blank row was inserted]
[row 9] 26 Sep input products 20
[row 10] 26 Sep output products -15
[row 11] 26 Sep input products 8
[row 12] 26 Sep input products 3
[row 13] 26 Sep output products -10
[row 14] 26 Sep input products 6
[row 15] 26 Sep output products -5
[row 16] 26 Sep input products 4
[row 17] 26 Sep keep inventories 11 = 20-15+8+3-10+6-5+4, which sum up the
available inventories for today only.
[row 18] [a blank row was inserted]

Try entering this in D7 (obviously you cannot enter it in column C or it will
screw up the blank row between your data). It is an array formula, so you
must enter it using Cntrl+Shift+Enter or you'll get #VALUE!

=SUM(INDEX(C$1:C6,MAX(ISBLANK(C$1:C6)*ROW(C$1:C6)),1):C6)

Then copy to D13, etc.

Could anyone please give me any suggestion on how to code following condition
in excel?

In colume A, it displays the date for activities.

In colume B, it displays a list of activities for everyday, such as input
products, output products and keep inventories. The number of activities is
not fixed, one day could performance 6 activities, but on the other days, it
could performance 9 activities, but a blank row must be inserted between two
days for separation.
the activities of "keep inventories" will only be performed once at the end
for each day, which will sum up all the quantity of products for today only.

In colume C, it displays the number of quantity for products.

Does anyone know how to code the condition in excel to sum up all the number
of quantity from above until it meets a blank row? please see below example
for detail description.

On 25 Sep, 2006, it contains a lists of activities as show below

[row 1] [a blank row was inserted]
[row 2] 25 Sep input products 10
[row 3] 25 Sep output products -5
[row 4] 25 Sep input products 7
[row 5] 25 Sep input products 6
[row 6] 25 Sep output products -15
[row 7] 25 Sep keep inventories 3 = 10-5+7+6-15, which sum up the available
inventories for today only.
[row 8] [a blank row was inserted]
[row 9] 26 Sep input products 20
[row 10] 26 Sep output products -15
[row 11] 26 Sep input products 8
[row 12] 26 Sep input products 3
[row 13] 26 Sep output products -10
[row 14] 26 Sep input products 6
[row 15] 26 Sep output products -5
[row 16] 26 Sep input products 4
[row 17] 26 Sep keep inventories 11 = 20-15+8+3-10+6-5+4, which sum up the
available inventories for today only.
[row 18] [a blank row was inserted]

Does anyone know how to code this situation in excel?
Thank you for any suggestion
Eric

 

[Guest]

That's what it did on my machine. Is C8 actually empty? Enter =ISBLANK(C8)
in another cell - what do you get?

I entered the formula in C7 (using the data you posted), then copied it to
C26 and get 3 and 11 respectively (with cell C8 being *truly* empty).

You could also go to C7 and try hitting Alt = and Excel should insert
=SUM(C2:C6) for you- same for cell C26.

If there are spaces in cell C8 making it appear empty, try:
=SUM(INDEX(C$1:C6,MAX((LEN(TRIM(C$1:C6))=0)*ROW(C$1:C6)),1):C6)
also committed w/Cntrl+Shift+Enter

Thank you for your suggestion

The formula solves part of the problem, it sums up all the value from above.
I would like to skip any value above the blank cell, such as
For the row 17, the sum of value would skip the value from row 1 to 8, and
only sum up the value from row 9 to 16.

Do you have any suggestion?
Thank you very much for your help
Eric

[row 1] [a blank row was inserted]
[row 2] 25 Sep input products 10
[row 3] 25 Sep output products -5
[row 4] 25 Sep input products 7
[row 5] 25 Sep input products 6
[row 6] 25 Sep output products -15
[row 7] 25 Sep keep inventories 3 = 10-5+7+6-15, which sum up the available
inventories for today only.
[row 8] [a blank row was inserted]
[row 9] 26 Sep input products 20
[row 10] 26 Sep output products -15
[row 11] 26 Sep input products 8
[row 12] 26 Sep input products 3
[row 13] 26 Sep output products -10
[row 14] 26 Sep input products 6
[row 15] 26 Sep output products -5
[row 16] 26 Sep input products 4
[row 17] 26 Sep keep inventories 11 = 20-15+8+3-10+6-5+4, which sum up the
available inventories for today only.
[row 18] [a blank row was inserted]

Try entering this in D7 (obviously you cannot enter it in column C or it will
screw up the blank row between your data). It is an array formula, so you
must enter it using Cntrl+Shift+Enter or you'll get #VALUE!

=SUM(INDEX(C$1:C6,MAX(ISBLANK(C$1:C6)*ROW(C$1:C6)),1):C6)

Then copy to D13, etc.

Could anyone please give me any suggestion on how to code following condition
in excel?

In colume A, it displays the date for activities.

In colume B, it displays a list of activities for everyday, such as input
products, output products and keep inventories. The number of activities is
not fixed, one day could performance 6 activities, but on the other days, it
could performance 9 activities, but a blank row must be inserted between two
days for separation.
the activities of "keep inventories" will only be performed once at the end
for each day, which will sum up all the quantity of products for today only.

In colume C, it displays the number of quantity for products.

Does anyone know how to code the condition in excel to sum up all the number
of quantity from above until it meets a blank row? please see below example
for detail description.

On 25 Sep, 2006, it contains a lists of activities as show below

[row 1] [a blank row was inserted]
[row 2] 25 Sep input products 10
[row 3] 25 Sep output products -5
[row 4] 25 Sep input products 7
[row 5] 25 Sep input products 6
[row 6] 25 Sep output products -15
[row 7] 25 Sep keep inventories 3 = 10-5+7+6-15, which sum up the available
inventories for today only.
[row 8] [a blank row was inserted]
[row 9] 26 Sep input products 20
[row 10] 26 Sep output products -15
[row 11] 26 Sep input products 8
[row 12] 26 Sep input products 3
[row 13] 26 Sep output products -10
[row 14] 26 Sep input products 6
[row 15] 26 Sep output products -5
[row 16] 26 Sep input products 4
[row 17] 26 Sep keep inventories 11 = 20-15+8+3-10+6-5+4, which sum up the
available inventories for today only.
[row 18] [a blank row was inserted]

Does anyone know how to code this situation in excel?
Thank you for any suggestion
Eric

 

[Guest]

Thank you very much
Eric