Is ther a way that you can sum the totals in an unkown range based on a value
in another column? First column have values inputted by user. Second column
have a letter identifing what the value in the first column means (example
"U" means unit, "B" means branch, branch is the sum of the units above it and
"J" means joint the branches together). What I am trying to do is sum the
first B all of the Units above it. Then sum the next B with all of the units
above it without including the Us above the previous B. Next I need to sum
the Js with all of the Bs above it without summing the Bs above the previous
J. Any help?

I think you'll need to post a sample with an explanation and the expected
result.

--
Biff
Microsoft Excel MVP


"David G" <David wrote in message
...
Is ther a way that you can sum the totals in an unkown range based on a
value
in another column? First column have values inputted by user. Second
column
have a letter identifing what the value in the first column means (example
"U" means unit, "B" means branch, branch is the sum of the units above it
and
"J" means joint the branches together). What I am trying to do is sum the
first B all of the Units above it. Then sum the next B with all of the
units
above it without including the Us above the previous B. Next I need to sum
the Js with all of the Bs above it without summing the Bs above the
previous
J. Any help?

Below is a sample with the desired results.

Unit # Amount Type accumlative
1 10 U 10
2 20 U 20
3 30 U 30
B 60
4 40 U 40
5 50 U 50
B 90
J 150
Unit #1 has a value of 10 and a type of U so the accumative value is 10.
Unit # 2 has a value of 20 and a type of U so the accumative value is 20.
Unit #3 has a value of 30 and a type of U so the accumative value is 30.
The next Unit is blank and the type is B so the accumative value is the sum
of the units above or 60 in this case.
Unit #4 has a value of 40 and a type of U so the accumative value is 40.
Unit #5 has a value of 50 and a type of U so the accumative value is 50.
The next unit is blank and the type is B so the accumative value is the sum
of the units above or 90 in this case.
The next unit is blank and the type is J so the accumative value is the sum
of all of the B above or in this case 150.

The number of Units(U) can vary from project to project as can the
Branches(B) and the Joins(J).
"T. Valko" wrote:

I think you'll need to post a sample with an explanation and the expected
result.

--
Biff
Microsoft Excel MVP


"David G" <David wrote in message
...
Is ther a way that you can sum the totals in an unkown range based on a
value
in another column? First column have values inputted by user. Second
column
have a letter identifing what the value in the first column means (example
"U" means unit, "B" means branch, branch is the sum of the units above it
and
"J" means joint the branches together). What I am trying to do is sum the
first B all of the Units above it. Then sum the next B with all of the
units
above it without including the Us above the previous B. Next I need to sum
the Js with all of the Bs above it without summing the Bs above the
previous
J. Any help?

Good grief! <g

This would be extremely complicated to do without helper columns so I took
the easy way and did use helper columns.

Assume:

column A = Unit #
column B = Amount
column C = Type

Row 1 are the column headers with the actual data starting on row 2.

Use columns D and E as the helpers. You can hide these columns if you want
to.

Enter this formula in D2:

=IF(C2="U","B"&COUNTIF(C$2:C2,"B")+1,"")

Enter this formula in E2:

=IF(C2="U","J"&COUNTIF(C$2:C2,"J")+1,"")

Select both D2 and E2 and copy down as needed.

The totals will be in column F.

Enter this formula in F2:

=IF(C2="U",B2,IF(C2="B",SUMIF(D:D,"B"&COUNTIF(C$2: C2,"B"),B:B),IF(C2="J",SUMIF(E:E,"J"&COUNTIF(C$2:C 2,"J"),B:B),"")))

Copy down as needed.

This works on your sample data but I can't guarantee it'll work on your
actual data since I don't know every possible scenario you may have.

--
Biff
Microsoft Excel MVP


"David G" wrote in message
...
Below is a sample with the desired results.

Unit # Amount Type accumlative
1 10 U 10
2 20 U 20
3 30 U 30
B 60
4 40 U 40
5 50 U 50
B 90
J 150
Unit #1 has a value of 10 and a type of U so the accumative value is 10.
Unit # 2 has a value of 20 and a type of U so the accumative value is 20.
Unit #3 has a value of 30 and a type of U so the accumative value is 30.
The next Unit is blank and the type is B so the accumative value is the
sum
of the units above or 60 in this case.
Unit #4 has a value of 40 and a type of U so the accumative value is 40.
Unit #5 has a value of 50 and a type of U so the accumative value is 50.
The next unit is blank and the type is B so the accumative value is the
sum
of the units above or 90 in this case.
The next unit is blank and the type is J so the accumative value is the
sum
of all of the B above or in this case 150.

The number of Units(U) can vary from project to project as can the
Branches(B) and the Joins(J).
"T. Valko" wrote:

I think you'll need to post a sample with an explanation and the expected
result.

--
Biff
Microsoft Excel MVP


"David G" <David wrote in message
...
Is ther a way that you can sum the totals in an unkown range based on a
value
in another column? First column have values inputted by user. Second
column
have a letter identifing what the value in the first column means
(example
"U" means unit, "B" means branch, branch is the sum of the units above
it
and
"J" means joint the branches together). What I am trying to do is sum
the
first B all of the Units above it. Then sum the next B with all of the
units
above it without including the Us above the previous B. Next I need to
sum
the Js with all of the Bs above it without summing the Bs above the
previous
J. Any help?

"T. Valko" wrote...
....
This would be extremely complicated to do without helper columns . . .
....

Not really.

Assume:

column A = Unit #
column B = Amount
column C = Type

Row 1 are the column headers with the actual data starting on row 2.
....

So far, so good. A single formula would only be possible if the data started
in row 2. But try this array formula.

D2 [array formula]:
=IF(C2="U",B2,IF(C2="B",SUM(INDEX(D$1:D1,MATCH(2,1/(C$1:C1<"U"))+1):D1),
IF(C2="J",SUMIF(C$1:C1,"B",D$1:D1),"invalid")))

Fill D2 down into D3:D9. Produces the following table for me.

1__10__U___10
2__20__U___20
3__30__U___30
_______B___60
4__40__U___40
5__50__U___50
_______B___90
_______J__150

Note: the formula would return #N/A in D2 if C2 contained B.

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
This would be extremely complicated to do without helper columns . . .
...

Not really.

Assume:

column A = Unit #
column B = Amount
column C = Type

Row 1 are the column headers with the actual data starting on row 2.
...

So far, so good. A single formula would only be possible if the data
started in row 2. But try this array formula.

D2 [array formula]:
=IF(C2="U",B2,IF(C2="B",SUM(INDEX(D$1:D1,MATCH(2,1/(C$1:C1<"U"))+1):D1),
IF(C2="J",SUMIF(C$1:C1,"B",D$1:D1),"invalid")))

Fill D2 down into D3:D9. Produces the following table for me.

1__10__U___10
2__20__U___20
3__30__U___30
_______B___60
4__40__U___40
5__50__U___50
_______B___90
_______J__150

Note: the formula would return #N/A in D2 if C2 contained B.

That's not really complicated but I just couldn't "see" it when I was
working on this.

We get different results for the last instance of J, however. You're summing
*all* instances of B, not the B's between the next to last and last instance
of J.

Here's a small sample file with additional data.

http://cjoint.com/?htuybzXqTx

--
Biff
Microsoft Excel MVP