Count the unique entries in a column of data

[Guest]

Hi,

I have:

Col A Col B
Dog 5
Cat 3
Boy 2
Dog 3
Cat 9
Boy 1


How can I "filter" this list down so it shows that I have:

Col A Col B
Dog 8
Cat 12
Boy 3

I need to bulk unique entries, while adding their counts (sum) together.

Thanks in advance for any suggestions.

 

[Guest]

you will have to do 2 things her, use the Unique filter and then use SUMIF
formula.

Assuming that you have data in Col A and B...
Select Col A
Use Advance filter
select unique records
select Copy to and give address as say D1
click OK

The above will list the unique records.

Now in col E, next to each unique record type the SUMIF formula as
=SUMIF(A1:A10,E1,B1:B10)
This will total the values.

HTH,

 

[Ron Coderre]

Have you considered using a Pivot Table?

First...Put column headings above the data (eg Type, Amount)

Then...from the Excel Main Menu: <Data><Pivot Table>
Use: Excel .......Click [Next]
Select your data .......Click [Next]

Click the [Layout] button
ROW: Drag the Type field here
DATA: Drag the Amount field here
If it doesn't list as Sum of Amount...dbl-click it and set it to SUM
Click [OK]
Select where you want the Pivot Table.......Click [Finish]

That will list each Type and the corresponding sum.

To refresh the Pivot Table, just right click it and select Refresh Data

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

One quick way ... use a pivot table (PT)

Insert 2 top col labels, eg:

Type No
Dog 5
Cat 3
etc

Select any cell within the table
Click Data > Pivot table. Click Next > Next.

In step 3 of the wiz. click Layout
Drag n drop Type within the ROW area
Drag n drop No within the DATA area
Click OK > Finish. That's it.

Hop over to the PT sheet for the results ..