Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the unique entries in a column of data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the unique entries in a column of data
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, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Remacricky" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the unique entries in a column of data
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) "Remacricky" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the unique entries in a column of data
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Remacricky" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique entries in data range | Excel Worksheet Functions | |||
Count unique entries... | Excel Worksheet Functions | |||
Count Unique Entries | Excel Worksheet Functions | |||
Count of unique entries | Excel Discussion (Misc queries) | |||
How do I count or display unique data in a column? | Excel Worksheet Functions |