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.

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

http://www.vonixx.com


"Nad" wrote:

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.

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)

"Nad" wrote:

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.

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$6 5536),"")

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

Ken Johnson

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

"Ken Johnson" wrote:

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$6 5536),"")

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

Ken Johnson

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

Ken Johnson

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)

"ANdras" wrote:

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)

"Nad" wrote:

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.

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

"Nad" wrote:

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)

"ANdras" wrote:

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)

"Nad" wrote:

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.

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


"Nad" wrote in message
...
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.

Nad Wrote:
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.

Check this site for sumproduct,

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=533259

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.



"Nad" wrote:

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.