salect USer and sum points

[seriousdamage]

HI,
I have a files containing names and relative points,
each day I add a new line of name with points like below,
is there a formula that can select all the same names and sum there points giving me the total?

Column A ---Column B
Paul 2
Mark 2
John 1
Paul 4
Paul 1
Mark 4

The result I need is
Paul = 7
Mark = 6
John = 1

Thanks a lot.

 

[Richard Schollar]

Hi

Use SUMIF:

=SUMIF(A1:A6,"Paul",B1:B6)

The "Paul" criteria can be a cell reference eg C1 if C1 holds Paul.

Best regards]


Richard

 

[seriousdamage]

Hi Richard, it workes just perfect.
Thank you so much.

Nic

 

[seriousdamage]

HI Richard or All,
I need to make it more complicated,
I have added a column called Week

At the moment your Statement says something like : SUM all Paul in column b and give me the total.

I need something like : SUM all Paul in column B and Week 1 in column A and give me the total.

I can I add a second statement?

Thanks
Nic

 

[Richard Schollar]

Hi Nic

So you have Weeks in colA, Name in colB and numbers to sum in col C? then:

=SUMPRODUCT(--(A1:A6=1),--(B1:B6="Paul"),C1:C6)

Richard

 

[seriousdamage]

OK, thanks again, but let's make it even more complicated.
I took the week column off, so I have Name in column A and numbers in Column B.

Step 1
I need to count the names and the total needs to be multiplied by 2

Step 2
I use the original formula above to SUM the points:
=SUMIF(A1:A6,"Paul",B1:B6)

Step 3
I need a match between the total of Step 1 and and Step 2, and the result has to be in percentage.

Example
if in Step 1 I have entered the name Paul 5 times, then the result should be 10.
in step two the SUM will give me a result of 4 points.

Then the result of step 3 should be 40%
which is calculated with the following logic,
if then is 100% the 4 is 40%.

Hope it makes sense.

Thanks
Nic

 

[Richard Schollar]

You can carry out all the steps in:

=SUMIF(A1:A6,"Paul",B1:B6)/(COUNTIF(A1:A6,"Paul")*2)

and format the cell as a percentage.