Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF problem...
My scenario
I have a sheet that shows points scored by various teams in a championship across several weeks Each team has two team members There are 10 teams. Each team member has a unique number assigned (1 through 21 (excludes #13)) EG Team A 1 John Doe 2 Fred Smith etc... In the sheet, I record the points they score. In hidden cells I filter these points to produce score charts showing a) Individual scores (IE Highest scorer at top) b) Team scores - combines the two players scores (again highest at top) This all worked well, until this year one player switched teams. For Individual scores, I need to show his combined score For Team score, I need his score separate for each team totalled - this aspect is fine, and I have that worked out. My problem is: In the refinining/filtering/sorting - call it what you may- process, I was using SUMIF to get the players individual number In this example, he has been number 15 and 18, so the sumif process lists him twice, both times with the number 33 and his points show twice of course (but each time the total score, not the individual score for each team so I get 33 John Doe Team A 1 33 John Doe Team B 1 where it should be 15 John Doe Team A 0 18 John Doe Team B 1 Is there a better way to do this to combine them properly. I think ultimately I'd like to see him in the indivdual chart as 15/18 John Doe Team A/Team B 1 Thanks Neil |
#2
|
|||
|
|||
Neil,
You need to change your SUMIF to a different SUMIF or a SUMPRODUCT. Presumably, you have a formula like =SUMIF(A1:A1000, "John Doe", B1:B1000) Then you would need to use something like: =SUMIF(C1:C1000, "Player 15", B1:B1000) =SUMPRODUCT((A1:A1000="John Doe")*(C1:C1000 = "Team 1")*(B1:B1000)) Of course, the actual ranges need to reflect where your data resides. And, in reality, a pivot table would be your best solution. HTH, Bernie MS Excel MVP wrote in message oups.com... My scenario I have a sheet that shows points scored by various teams in a championship across several weeks Each team has two team members There are 10 teams. Each team member has a unique number assigned (1 through 21 (excludes #13)) EG Team A 1 John Doe 2 Fred Smith etc... In the sheet, I record the points they score. In hidden cells I filter these points to produce score charts showing a) Individual scores (IE Highest scorer at top) b) Team scores - combines the two players scores (again highest at top) This all worked well, until this year one player switched teams. For Individual scores, I need to show his combined score For Team score, I need his score separate for each team totalled - this aspect is fine, and I have that worked out. My problem is: In the refinining/filtering/sorting - call it what you may- process, I was using SUMIF to get the players individual number In this example, he has been number 15 and 18, so the sumif process lists him twice, both times with the number 33 and his points show twice of course (but each time the total score, not the individual score for each team so I get 33 John Doe Team A 1 33 John Doe Team B 1 where it should be 15 John Doe Team A 0 18 John Doe Team B 1 Is there a better way to do this to combine them properly. I think ultimately I'd like to see him in the indivdual chart as 15/18 John Doe Team A/Team B 1 Thanks Neil |
#3
|
|||
|
|||
Bernie
SUMPRODUCT worked a treat, with a bit of alteration using a cell reference instead of "John Doe" and "Team 1" Sorry for late reply, other thigs took over and I forgot to update my sheet Thanks again Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Problem | Excel Worksheet Functions | |||
SUMIF problem | Excel Worksheet Functions | |||
SUMIF problem | Excel Worksheet Functions | |||
SUMIF problem | Excel Worksheet Functions | |||
SUMIF problem | Excel Worksheet Functions |