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.

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!

this will work from row 2

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

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))

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

> 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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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

Just copy down however many rows!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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


On Sep 23, 2:55*pm, Rob P <r.p.philli...@gmail.com> wrote:
> this will work from row 2
>
> =IF(A2="","",IF(A1=A2,C1+B2,B2))

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



On Sep 23, 7:04*pm, GS <g...@somewhere.net> wrote:
> Justto be clear, the new formula in C1 is...
>
> * =IF(A1="","",IF(LastCell="",B1,LastCell+B1))
>
> Just copy down however many rows!
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

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


On Sep 19, 11:38*am, strawberry <zac.ca...@gmail.com> wrote:
> 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!

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...
>
>
>
> On Sep 23, 7:04*pm, GS <g...@somewhere.net> wrote:
>> Justto be clear, the new formula in C1 is...
>>
>> * =IF(A1="","",IF(LastCell="",B1,LastCell+B1))
>>
>> Just copy down however many rows!
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc