Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing specific Columns based on Criteria
Hello,
I am running into a bit of a problem so if anyone could figure it out I would be very greatful. I have the following Table: Sport Student Jan-07 Feb-07 Mar-07 Apr-07 May-07 Football Ted 30 4 10 15 20 Bastketball Ball 40 2 151 1 3 Track Jacob 1 44 6 2 345 Swimming Monty 6 647 4 8 17 Tennis Bill 15 3 9 784 75 Bastketball Jacob 234 578 36 5 4 Track Monty 75 7 75 2 90 Track Ted 57 1 5 1 7 Football Chad 5 7 2 245 9 Bastketball David 33 4 7 69 8 Football Ted 4 5 6 7 4 Bastketball Bill 3 18 88 54 634 Track Jacob 7 9 2 75 75 Swimming Monty 4 6 7 3 86 Tennis Ted 36 9 82 96 46 What I am trying to do is to add up the total for each student per month into another table which would look like this: Student Ted Total Jan-07 Feb-07 Mar-07 Apr-07 May-07 Football Bastketball Track Swimming Tennis Does anyone know exactly how I would be able to do this? Thanks, Coldheat |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing specific Columns based on Criteria
Assuming data in your first table is on Sheet1 in columns A to G
In Sheet2: B1=Student Name B2=Jan-07, C2=Feb-07 etc A3=Football A4=Basketball etc in B3: =SUMPRODUCT(--(Sheet1!$B$2:$B$16=$B$1),--(Sheet1!$A$2:$A$16=$A3),--(Sheet1!C$2:C$16)) Copy across and down Changes ranges on sheet1 as required HTH " wrote: Hello, I am running into a bit of a problem so if anyone could figure it out I would be very greatful. I have the following Table: Sport Student Jan-07 Feb-07 Mar-07 Apr-07 May-07 Football Ted 30 4 10 15 20 Bastketball Ball 40 2 151 1 3 Track Jacob 1 44 6 2 345 Swimming Monty 6 647 4 8 17 Tennis Bill 15 3 9 784 75 Bastketball Jacob 234 578 36 5 4 Track Monty 75 7 75 2 90 Track Ted 57 1 5 1 7 Football Chad 5 7 2 245 9 Bastketball David 33 4 7 69 8 Football Ted 4 5 6 7 4 Bastketball Bill 3 18 88 54 634 Track Jacob 7 9 2 75 75 Swimming Monty 4 6 7 3 86 Tennis Ted 36 9 82 96 46 What I am trying to do is to add up the total for each student per month into another table which would look like this: Student Ted Total Jan-07 Feb-07 Mar-07 Apr-07 May-07 Football Bastketball Track Swimming Tennis Does anyone know exactly how I would be able to do this? Thanks, Coldheat |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing specific Columns based on Criteria
Toppers,
Thank you very much for your help that did the trick, but is there a way also to include the months? Just incase I want to change the monts or the table for in sheet1 changes? Thanks, Coldheat |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing specific Columns based on Criteria
With same layout as previous posting, try:
=SUMPRODUCT(--(Sheet1!$B$2:$B$16=$B$1)*(Sheet1!$A$2:$A$16=$A3)*( Sheet1!$C$1:$G$1=B$2)*(Sheet1!$C$2:$G$16)) HTH " wrote: Toppers, Thank you very much for your help that did the trick, but is there a way also to include the months? Just incase I want to change the monts or the table for in sheet1 changes? Thanks, Coldheat |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing specific Columns based on Criteria
Toppers,
Thanks again, you have been a big help!! Coldheat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a range based on value criteria | Excel Discussion (Misc queries) | |||
Summing data based on criteria | Excel Discussion (Misc queries) | |||
Summing based on criteria | Excel Worksheet Functions | |||
Summing quantities based on like criteria? | Excel Discussion (Misc queries) | |||
Summing based on 2 criteria | Excel Discussion (Misc queries) |