help me

N

Nad

i have the following data
Name Score
AB 10
CD 15
AB 05
EF 20
CD 30
AB 50
I want to dispaly only the unique records with their total score.(e.g. AB
65)
Pls help, i am trying since last three days.
 
E

Edwin Tam

What you need is a Pivot Table.

1) With any one cell of your table selected, from the Data menu, choose
"Pivot Table & Pivot Chart Report"
2) Click the next button twice
3) Click the "Layout" button.
4) Use the mouse to drag the "Name" (from the right) onto the "ROW" area.
5) Drag the "Score" onto the "Data" area
6) Click OK
7) Click Finish

Regards,
Edwin Tam
[email protected]
http://www.vonixx.com
 
A

ANdras

Hello Nad,

i believe, you can create a PivotTable, that will have the desired result
(every occurance is shown only once and their Score can be summed up.

in Office 2003, Data menu/Pivot table wizard, then follow the wizards' step
(help is available) and the drag&drop the Name field to Row, the Score field
to Data and voilá :)

alternatively, in case you have an additional list of every occurance, you
could use the SUMIF function as well.

please, let me know, if you need further help.

Best regards,
ANdras
(Hungary)
 
C

CLR

Not precisely what you asked for, but a nice display of the data would be to
alphabetize on the name column, then do Data > Subtotals > At each change in
: NAME > Use function: SUM > Add subtotal to: check the SCORE box > OK

Vaya con Dios,
Chuck, CABGx3
 
K

Ken Johnson

Hi Nad,
Assuming the range of Names are in column A and scores are in column B
then the following formula in column C will return the names but only
in the first row that they appear eg AB in row 2 but not again in rows
4 or 7...

=IF(COUNTIF(A$2:A$65536,A2)-COUNTIF(A2:A$65536,A2)=0,A2,"")

There total scores can be returned in column D using...

=IF(C2<>"",SUMPRODUCT(($A$2:$A$65536=A2)*$B$2:$B$65536),"")

You can then apply auto filter to hide the blank rows then copy and
paste to wherever.

Ken Johnson
 
N

Nad

thanks for your help
I don't want to use Pivot table.Also i dont want to use Sumif function
because i have more than 100 names because i have to write sumif function for
every name.

i want to display the sum of score of particular name in adjacent column.
(i can delete the score column and want to display total score column)
 
B

bpeltzer

If you make the 2nd argument of the sumif function a cell reference rather
than a fixed name, you can autofill the formula and not have to enter it 100
times. That is, instead of =sumif(A:A,"Nad",B:B), use =sumif(A:A,E2,B:B) if
the list of unique names begins in cell E2. Then just copy that formula from
F2 all the way down to the end of your list of unique names.
(FWIW, the Pivot Table would have been my suggestion as well; it will
determine all the unique names and total the associated scores).
--Bruce
 
M

Manoj

Pivot table is the best option... It summarises data very well and can be
refreshed at any time. Double click on any of the data cell to get the
records that built up that sum. This is one of the best functions of the
pivot - i.e., a drill down.
 
N

Nad

Hi Ken,
Its amazing. I got my result exactly the way i want.
Thank U very much indeed.
Regards
Nad
 
K

Ken Johnson

Hi Nad,
Great to hear you've had success!
SUMPRODUCT is an amazing function.
Thanks for the feedback.

Ken Johnson
 
Top