Another Running Totals Problem

[F J]

Hi, I have a several large spreadsheets which have data that is broken
into groups. Each of these groups needs a running total within its
own group. I had some similar spreadsheets the other day and someone
here gave me a great formula that worked on those using the data in
column A as the criteria for a SUMIF formula:

=IF(A1="","",SUMIF($A$1:A1,A1,$B$1:B1))

The problem with these new files is that unlike the other ones, there
is nothing that is always unique about the data in each group that
could be used for a SUMIF formula or something like that. The only
indication that the data should even be in separate groups is that
each group is separated by a blank row. The data is set up like
this. The running totals formula would appear in column C:

Column A Column B Column C
Apples 100 100
Apples 200 300
Apples 500 800

Apples 300 300
Apples 100 400

Oranges 200 200
Oranges 300 500

Oranges 500 500

Pears 300 300
Pears 200 500

Apples 200 200
Apples 500 700

Is there any way to create a formula that can just be copied all the
way down the column instead of having to go through and change it for
each group? Thank you in advance for any help or information.

 

[strawberry]

So if cell A in the preceding row is blank, Cn=B, otherwise Cn=B+(Cn-1)

I'm far from expert but that seems pretty easy to me!

 

[Rob P]

this will work from row 2

=IF(A2="","",IF(A1=A2,C1+B2,B2))

 

[GS]

F J wrote on 9/18/2011 :

Hi, I have a several large spreadsheets which have data that is broken
into groups. Each of these groups needs a running total within its
own group. I had some similar spreadsheets the other day and someone
here gave me a great formula that worked on those using the data in
column A as the criteria for a SUMIF formula:

=IF(A1="","",SUMIF($A$1:A1,A1,$B$1:B1))

The problem with these new files is that unlike the other ones, there
is nothing that is always unique about the data in each group that
could be used for a SUMIF formula or something like that. The only
indication that the data should even be in separate groups is that
each group is separated by a blank row. The data is set up like
this. The running totals formula would appear in column C:

Column A Column B Column C
Apples 100 100
Apples 200 300
Apples 500 800

Apples 300 300
Apples 100 400

Oranges 200 200
Oranges 300 500

Oranges 500 500

Pears 300 300
Pears 200 500

Apples 200 200
Apples 500 700

Is there any way to create a formula that can just be copied all the
way down the column instead of having to go through and change it for
each group? Thank you in advance for any help or information.

Starting in C2:

=IF(A2="","",IF(C1="",B2,C1+B2))

 

[GS]

Starting in C2:

=IF(A2="","",IF(C1="",B2,C1+B2))

If you need to start in row 1 then you can create a local defined name
as follows:

Select A2
In the DefineName dialog:
In the NameBox type LastCell
In the RefersTo box type =A1
Click the 'Add' button and close the dialog

In the formula above, replace C1 with LastCell

 

[GS]

Justto be clear, the new formula in C1 is...

=IF(A1="","",IF(LastCell="",B1,LastCell+B1))

Just copy down however many rows!

 

[F J]

Hi, Rob, thanks for your response. Your formula works great.

 

[F J]

Hi, GS, thanks for your replies. I tried both of your formulas and
they work great.

 

[F J]

Hi, strawberry, thanks for your reply. I wasn't sure exactly how to
apply your formula, but I will look into it.

 

[GS]

F J wrote on 9/25/2011 :

Hi, GS, thanks for your replies. I tried both of your formulas and
they work great.

Glad to help. Thanks for the feedback!
Best wishes...