Summing specific Columns based on Criteria

[gilmore.darrell]

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

 

[Toppers]

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

 

[gilmore.darrell]

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

 

[Toppers]

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

 

[gilmore.darrell]

Toppers,

Thanks again, you have been a big help!!


Coldheat