sum up to nested groups
 

Hi Everyone,

I have one more question about totalling to multiple groups. As I posted in
the following, I want to total credit hours for full time (F Total) and part
time (P Total) faculty within each department:

Department Faculty Credit
ANTHROPOLOGY F Total 76
ANTHROPOLOGY P Total 37
ART F Total 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

I want to get the results like

Department Faculty Credit FCredit
PCredit
ANTHROPOLOGY F Total 76 76 37
ANTHROPOLOGY P Total 37
ART F Total 87 164 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32 108 12
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

It seems complex. Who can help me figure out the codes for this purpose? I
will very appreciate your help. Thanks.


Charles

sum up to nested groups
 

Wouldn't it be easier if you make a pivot table?

Charles wrote:
Hi Everyone,

I have one more question about totalling to multiple groups. As I posted in
the following, I want to total credit hours for full time (F Total) and part
time (P Total) faculty within each department:

Department Faculty Credit
ANTHROPOLOGY F Total 76
ANTHROPOLOGY P Total 37
ART F Total 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

I want to get the results like

Department Faculty Credit FCredit
PCredit
ANTHROPOLOGY F Total 76 76 37
ANTHROPOLOGY P Total 37
ART F Total 87 164 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32 108 12
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

It seems complex. Who can help me figure out the codes for this purpose? I
will very appreciate your help. Thanks.

Charles

--
Message posted via http://www.officekb.com

sum up to nested groups
 

Assuming the first ANTRO is in A1, with "F Total" in B2 and 76 in C2:
In D2 enter =SUMPRODUCT(--($A$2:$A$10=$A2),--($B$2:$B$10="F
Total"),$C$2:$C$10)
Copy this across to E2 and modify E2 changing F to P
=SUMPRODUCT(--($A$2:$A$10=$A2),--($B$2:$B$10="P Total"),$C$2:$C$10)
Copy D2 down to D3 and change D3 to read
=IF($A3<$A2,SUMPRODUCT(--($A$2:$A$10=$A3),--($B$2:$B$10="F
Total"),$C$2:$C$10),"")
Copy D3 to E3 and aging change F to P
=IF($A3<$A2,SUMPRODUCT(--($A$2:$A$10=$A3),--($B$2:$B$10="P
Total"),$C$2:$C$10),"")
best wishes from a former university registrar
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Charles" wrote in message
...


Hi Everyone,

I have one more question about totalling to multiple groups. As I posted
in
the following, I want to total credit hours for full time (F Total) and
part
time (P Total) faculty within each department:

Department Faculty Credit
ANTHROPOLOGY F Total 76
ANTHROPOLOGY P Total 37
ART F Total 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

I want to get the results like

Department Faculty Credit FCredit
PCredit
ANTHROPOLOGY F Total 76 76 37
ANTHROPOLOGY P Total 37
ART F Total 87 164
87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32 108 12
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

It seems complex. Who can help me figure out the codes for this purpose?
I
will very appreciate your help. Thanks.


Charles

sum up to nested groups
 

Assuming the first cell is A1
Then in D2
=IF($A1<$A2,SUMPRODUCT(--($A$2:$A$10=$A2),--($B$2:$B$10="F
Total"),$C$2:$C$10),"")
In E2
=IF($A1<$A2,SUMPRODUCT(--($A$2:$A$10=$A2),--($B$2:$B$10="P
Total"),$C$2:$C$10),"")

"Charles" wrote:



Hi Everyone,

I have one more question about totalling to multiple groups. As I posted in
the following, I want to total credit hours for full time (F Total) and part
time (P Total) faculty within each department:

Department Faculty Credit
ANTHROPOLOGY F Total 76
ANTHROPOLOGY P Total 37
ART F Total 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

I want to get the results like

Department Faculty Credit FCredit
PCredit
ANTHROPOLOGY F Total 76 76 37
ANTHROPOLOGY P Total 37
ART F Total 87 164 87
ART P Total 24
ART F Total 77
ART P Total 63
BIOLOGICAL SCIENCES F Total 32 108 12
BIOLOGICAL SCIENCES P Total 12
BIOLOGICAL SCIENCES F Total 76

It seems complex. Who can help me figure out the codes for this purpose? I
will very appreciate your help. Thanks.


Charles