Excel: aggregate range depending on the first cell

[cheer8923]

Not too familiar with Excel. Here is what I want to do:

A B
1 5/1 5
2 6
3 7
4 5/2 8
5 9
6 10
7 11
8 5/3 12
9 13

I would want make

A2 = B1 + B2 + B7
A5 = SUM(B4:B7)
A9 = SUM(B8:B9)

Any suggestions?

Thanks!

 

[SkipVought]

Hi,

In A2 type...
[tt]
= B1 + B2 + B7
[/tt]
In A5 type...
[tt]
= SUM(B4:B7)
[/tt]
In A9 type...
[tt]
= SUM(B8:B9)
[/tt]
or am I missing something?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

Sorry that I did not make it clear. I want it to be done automatically. I have thousands of them and would want to use the same formula.

 

[Lilliabeth]

= B1 + B2 + B7
= SUM(B4:B7)

How are these "the same formula"? Did you mean to say B3 instead of B7 in the first one?

--Lilliabeth

 

[SkipVought]

What is the LOGIC related to which formula to use where?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

I want the same formula in A2, A5, and A9, which will essentially set its value as the following.

A2 = B1 + B2 + B7
A5 = SUM(B4:B7)
A9 = SUM(B8:B9)

This means somehow it has to search for the next value in column A and sum up B as long as cell in column A remains empty (or less than A)

 

[cheer8923]

Sorry, my mistake, A2 should be

A5 = SUM(B1:B3)

 

[SkipVought]

This is a job for a VBA procedure.

Seems like you have to search for 5/1 and insert the FIRST formula in the next cell, then search for 5/2 and insert the SECOND formula in the next cell, then search for 5/3 and insert the THIRD formula in the next cell, and keep doing that until there are no more 5/1, 5/2 or 5/3.

If that's the drill, please ALSO post these details in Forum707 when you repost your question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

can I set a formula in the cell to call the vba?

 

[SkipVought]

There are EVENTS that can be used like the worksheet change event, but I don't see how you would use a sheet event to call a procedure, unless it were one particular cell.

Please explain what your business case is.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cheer8923]

I was hoping excel has sth like

=SUM(A2:A[RowOf(FindNextNonEmptyCell(A5)])

 

[SkipVought]

Your data need to be organized like a proper table
[tt]
1 5/1 5
2 5/1 6
3 5/1 7
4 5/2 8
5 5/2 9
6 5/2 10
7 5/2 11
8 5/3 12
9 5/3 13
[/tt]
You can do this with a simple formula in the column C, But FIRST, you need a row of headings, which every proper table ought to have. Then in C2...
[tt]
=if(a2=a1,c1,a2)
[/tt]
and copy down thru all rows of data.

Copy column C and Edit > Paste Special - VALUES in column A. The delect column C

Make sure you have the Column A & B headings in row 1.

Select in your table.

Data > Subtotals is one way.

The SUMIF function is another way.
[tt]
C2: =SUMIF($A$2:$A$10,A2,$B$2:$B$10)
[/tt]
Of course, if your table goes to a column other than 10, use that column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}