SUMIF problem...

[neil40]

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

 

[Bernie Deitrick]

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

 

[neil40]

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