salect USer and sum points

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58
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.
 

Some videos you may like

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 14, 2005
Messages
58
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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
Joined
Aug 14, 2005
Messages
58
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
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,444
Members
410,610
Latest member
renatha prado
Top