|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 15th, 2009, 06:01 PM
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
May 15th, 2009, 06:21 PM
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
May 15th, 2009, 06:39 PM
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
May 15th, 2009, 06:52 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
