SumIf - need update formula + pivot table

**nitr0** · 08-11-2010, 04:19 PM

Hi, again..I have last problem
need to sum points if man is teammate with somebody.
I use =SUMIF(F:F;A2;C:C)
where F is "teammate column"
A2 is name of Man
C are points of each man
formula sholud do this: if somebody have in column C have teammate from A it sum points and put to TeamMate`s column B..but if put only one points and not sum of TeamMate himself.

it is better understand from sheet in attachement

and I need to make pivot table, but like this:

choose Month| Choose Year | Choose Name|

see OWN | SHARE| ACTUAL

sum only Actual from each year/month because own and share points have person same always and in case when it sum together can mistake happen, because One person can be several times in table

I try it but can sort only by names or date and always sum own and share together.

thanks for help
i dont know if you understand me because of my english but i hope yes

attachement in 2007, 2003or less + comment included

**meyero90** · 08-11-2010, 05:32 PM

Ok, Look at the spreadsheet I attached, everything is as requested (hopefully)

An Important Rule with PivotChart/PivotTable, I don't know if you're familiar with it, is that you can not make a change directly to the pivotTable numbers (for instance you can't change manually the value of C25), but be able to do it, you will have to go to the source of the data. Source of Data in our case is in the range A1:G7.

Last Thing if My solution is correct, you can just deselect "Points(shared)" from the PivotTable Field List.

As per the formula used it's the following

Please Login or Register  to view this content.

And it can be drag down if you add more name etc..BUT REMEMBER TO CHANGE THE "TABLE_ARRAY" RANGE OF THE VLOOKUP.

**meyero90** · 08-11-2010, 05:45 PM

Again..last thing with respect to the PivotChart.
In case you add more rows and/or columns, remember to change the "Source Data" as well.
Click anywhere on the PivotTable>Click on the Highlighted Red Tab on the Ribbon will appear>Under Data>Click on "Change Source Data">Select your new extended range in Select a table or range....

.And you should be good to go

**nitr0** · 08-11-2010, 06:09 PM

pivot chart is great..as i imagine, but that sum if dont work
because for example in second row 15730(the points of thomas jane)+1934(own) but have only own..

imagine it like that: TeamMate is like boss of the person.
so TeamMate have= OWN points of Person whose is TeamMate + OWN points

so if I am player and you are too, but you are my boss. so you have my points+your but I have only my.

**meyero90** · 08-11-2010, 06:15 PM

try this I changed the formula

**nitr0** · 08-11-2010, 06:23 PM

no..now it is wrong whole..thomas jane was good but now is bad too

too much points, but why?
he should have his points+rob miller points
will patrick = own+ thomas jane (own points)
and so on..

**meyero90** · 08-11-2010, 06:31 PM

Sorry

I'll try to fix later. I'm off now. I have to re-read again the way you wants the points to be calculated.

**nitr0** · 08-11-2010, 06:37 PM

ok, thank anyway..and one question to pivot..can i have name as filter and also in row name? because when i filter all names, i see dates, but cant see who..just question..when i choose one it is good..but all..

**meyero90** · 08-12-2010, 09:34 AM

Oh Boy, I don't know if I am the one who's thinking in a complicated way or it's simply because of your problem!
Anyway, hopefully this time is the correct formula. One last thing, Why in A7 Thomas Jane collects only 29323, shouldn't he collect 45053 from the points he earned with his Teammate Rob Miller as he did in A6. In A6 he collected 45053, so why not in A7?

I changed the formula directly in the file. but here it is and you can input in G2 and drag it down.

Please Login or Register  to view this content.

PAY ATTENTION TO " *1 " at the end of the formula, I just added to because if you take it out you will obtain "FALSE", because it doesn't find any teammates. So by multiplying FALSE by 1, it returns 0.

As per the PivotTable, you can not have the Name in the row, but what you can do is double click on any data in the pivottable and you'll see who the names are.

Hope I answered everything