sum up to nested groups

[Charles]

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

 

[Andy_N1708 via OfficeKB.com]

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

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

 

[Bernard Liengme]

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

 

[joel]

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),"")