Excel Conditional Summing
 

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?

Excel Conditional Summing
 

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?

Excel Conditional Summing
 

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?

Excel Conditional Summing
 

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?

Excel Conditional Summing
 

"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.

Excel Conditional Summing
 

"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

Excel Conditional Summing
 

"T. Valko" wrote...
....
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.
....

Didn't notice there could be multiple Js. Accomodating that leads to a
nonarray formula that scales much more easily if additional sumarization
levels are added.

D2:
=IF(C2="U",B2,SUMPRODUCT(D$1:D1,(C$1:C1=MID("UBJ", FIND(C2,"UBJ")-1,1))
*(ROW(C$1:C1)LOOKUP(2,1/((C$1:C1=C2)+(C$1:C1=C$1)),ROW(C$1:C1)))))

Fill D2 down as needed. Tested using the following sample.

U#__Amt__T___Sum
_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
_6___60__U____60
_7___70__U____70
_8___80__U____80
_9___90__U____90
_________B___300
10__100__U___100
11__110__U___110
_________B___210
12__120__U___120
13__130__U___130
14__140__U___140
_________B___390
_________J___900
16__160__U___160
17__170__U___170
18__180__U___180
_________B___510
19__190__U___190
_________B___190
20__200__U___200
_________B___200
21__210__U___210
22__220__U___220
_________B___430
_________J__1330


Here's a small sample file with additional data.

http://cjoint.com/?htuybzXqTx

If you have to use a workbook to explain what you mean, . . .

Excel Conditional Summing
 

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
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.
...

Didn't notice there could be multiple Js. Accomodating that leads to a
nonarray formula that scales much more easily if additional sumarization
levels are added.

D2:
=IF(C2="U",B2,SUMPRODUCT(D$1:D1,(C$1:C1=MID("UBJ", FIND(C2,"UBJ")-1,1))
*(ROW(C$1:C1)LOOKUP(2,1/((C$1:C1=C2)+(C$1:C1=C$1)),ROW(C$1:C1)))))

Fill D2 down as needed. Tested using the following sample.

U#__Amt__T___Sum
_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
_6___60__U____60
_7___70__U____70
_8___80__U____80
_9___90__U____90
_________B___300
10__100__U___100
11__110__U___110
_________B___210
12__120__U___120
13__130__U___130
14__140__U___140
_________B___390
_________J___900
16__160__U___160
17__170__U___170
18__180__U___180
_________B___510
19__190__U___190
_________B___190
20__200__U___200
_________B___200
21__210__U___210
22__220__U___220
_________B___430
_________J__1330


Here's a small sample file with additional data.

http://cjoint.com/?htuybzXqTx

If you have to use a workbook to explain what you mean, . . .

A picture is worth a thousand words!

--
Biff
Microsoft Excel MVP

Excel Conditional Summing
 

Thanks for your help. This one really stumped me. I have another one if you
are gamed. I am trying to change the color of the row based on the PM
initials in a column.

Thanks again,

David

"T. Valko" wrote:

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
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.
...

Didn't notice there could be multiple Js. Accomodating that leads to a
nonarray formula that scales much more easily if additional sumarization
levels are added.

D2:
=IF(C2="U",B2,SUMPRODUCT(D$1:D1,(C$1:C1=MID("UBJ", FIND(C2,"UBJ")-1,1))
*(ROW(C$1:C1)LOOKUP(2,1/((C$1:C1=C2)+(C$1:C1=C$1)),ROW(C$1:C1)))))

Fill D2 down as needed. Tested using the following sample.

U#__Amt__T___Sum
_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
_6___60__U____60
_7___70__U____70
_8___80__U____80
_9___90__U____90
_________B___300
10__100__U___100
11__110__U___110
_________B___210
12__120__U___120
13__130__U___130
14__140__U___140
_________B___390
_________J___900
16__160__U___160
17__170__U___170
18__180__U___180
_________B___510
19__190__U___190
_________B___190
20__200__U___200
_________B___200
21__210__U___210
22__220__U___220
_________B___430
_________J__1330


Here's a small sample file with additional data.

http://cjoint.com/?htuybzXqTx

If you have to use a workbook to explain what you mean, . . .

A picture is worth a thousand words!

--
Biff
Microsoft Excel MVP

Excel Conditional Summing
 

You need to explain it in more detail. We (I) like details! The more
information you provide, the easier it is to come up with a solution.

--
Biff
Microsoft Excel MVP


"David G" wrote in message
...
Thanks for your help. This one really stumped me. I have another one if
you
are gamed. I am trying to change the color of the row based on the PM
initials in a column.

Thanks again,

David

"T. Valko" wrote:

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
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.
...

Didn't notice there could be multiple Js. Accomodating that leads to a
nonarray formula that scales much more easily if additional
sumarization
levels are added.

D2:
=IF(C2="U",B2,SUMPRODUCT(D$1:D1,(C$1:C1=MID("UBJ", FIND(C2,"UBJ")-1,1))
*(ROW(C$1:C1)LOOKUP(2,1/((C$1:C1=C2)+(C$1:C1=C$1)),ROW(C$1:C1)))))

Fill D2 down as needed. Tested using the following sample.

U#__Amt__T___Sum
_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
_6___60__U____60
_7___70__U____70
_8___80__U____80
_9___90__U____90
_________B___300
10__100__U___100
11__110__U___110
_________B___210
12__120__U___120
13__130__U___130
14__140__U___140
_________B___390
_________J___900
16__160__U___160
17__170__U___170
18__180__U___180
_________B___510
19__190__U___190
_________B___190
20__200__U___200
_________B___200
21__210__U___210
22__220__U___220
_________B___430
_________J__1330


Here's a small sample file with additional data.

http://cjoint.com/?htuybzXqTx

If you have to use a workbook to explain what you mean, . . .

A picture is worth a thousand words!

--
Biff
Microsoft Excel MVP