Excel Conditional Summing

G

Guest

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?
 
G

Guest

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

T. Valko

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:C2,"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.
 
H

Harlan Grove

T. Valko said:
This would be extremely complicated to do without helper columns . . .
Click to expand...
....

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.
Click to expand...
....

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

T. Valko

Harlan Grove said:
T. Valko said:
This would be extremely complicated to do without helper columns . . .
Click to expand...
...

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.
Click to expand...
...

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.
Click to expand...

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
 
H

Harlan Grove

T. Valko said:
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.
Click to expand...
....

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
Click to expand...

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

T. Valko

Harlan Grove said:
...

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



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

A picture is worth a thousand words!
 
G

Guest

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

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum every Nth Row (almost working) 1
SUMIF 1
Sum Data if Column B = Q 3
Is it possible to SUM based on value of another column? 3
Electricity Bill Sheet 6
Really need some help with a multi conditional SumProduct 1
Sumproduct - meet multiple criteria from a list 6
SUM question 4

Top