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