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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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.
 

Forum statistics

Threads
1,141,011
Messages
5,703,726
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top