# salect USer and sum points

#### seriousdamage

##### Board Regular
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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Richard Schollar

##### MrExcel MVP
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

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

Nic

#### seriousdamage

##### Board Regular
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

##### MrExcel MVP

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

##### Board Regular
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

##### MrExcel MVP
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.

Replies
9
Views
109
Replies
7
Views
57
Replies
6
Views
58
Replies
15
Views
259
Replies
2
Views
33