| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Groups from IE | Excel Discussion (Misc queries) | |||
| Excel - Sorting groups in groups due to subtotaling | Excel Worksheet Functions | |||
| nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
| how can i show two layers of plus/minus signs for nested groups | Excel Discussion (Misc queries) | |||
| how do i view all groups under excel in google groups | Excel Discussion (Misc queries) | |||
Linear Mode
