Home

Count of Unique Values in Column

%3CLINGO-SUB%20id%3D%22lingo-sub-932580%22%20slang%3D%22en-US%22%3ECount%20of%20Unique%20Values%20in%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-932580%22%20slang%3D%22en-US%22%3E%3CP%3EMorning%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20stuck%20with%20a%20Problem%20that%20I'm%20not%20sure%20which%20formula%20will%20sort%20out%2C%20I%20got%20a%20Column%20with%20unique%20values%20in%2C%20now%20there%20are%20hundreds%20of%20these%20values%20and%20I%20don't%20know%20all%20of%20their%20names%20or%20how%20big%20the%20column%20will%20grow%20and%20I%20don't%20just%20want%20the%20quantity%20of%20the%20unique%20values%20but%20also%20the%20name's.%3C%2FP%3E%3CP%3ESo%2C%20let%E2%80%99s%20say%20we%20got%3A%3C%2FP%3E%3CP%3EApple%3C%2FP%3E%3CP%3EPear%3C%2FP%3E%3CP%3EOrange%3C%2FP%3E%3CP%3EBanana%3C%2FP%3E%3CP%3EGrape%3C%2FP%3E%3CP%3EOrange%3C%2FP%3E%3CP%3EApple%3C%2FP%3E%3CP%3EBanana%3C%2FP%3E%3CP%3EOrange%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20need%20a%20Formula%20that%20will%20tell%20me%20the%20value%20name%20and%20quantity%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApple%20%3D%202%3C%2FP%3E%3CP%3EPear%20%3D%201%3C%2FP%3E%3CP%3EOrange%20%3D%203%3C%2FP%3E%3CP%3EBanana%20%3D%202%3C%2FP%3E%3CP%3EGrape%20%3D%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20said%20I%20don%E2%80%99t%20know%20all%20the%20Unique%20Values%20so%20the%20Formula%20will%20have%20to%20pick%20them%20up%20from%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20hope%20there%20is%20a%20way%20of%20doing%20this%20else%20I%20will%20have%20to%20try%20and%20make%20a%20Separate%20Formula%20for%20Each%20Unique%20Value%20by%20going%20thru%20all%20the%20data%20and%20writing%20all%20the%20names%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApricate%20any%20Help%20Greatly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20Great%20Day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-932580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-933228%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Unique%20Values%20in%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-933228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430924%22%20target%3D%22_blank%22%3E%40MCI-IT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20pivot%20table%20where%20the%20data%20source%20refers%20to%20the%20entire%20column%20where%20your%20data%20sits.%20That%20way%2C%20you%20can%20keep%20on%20adding%20items%20to%20your%20list%20without%20having%20to%20update%20a%20static%20range%20every%20time%20you%20do%20so.%20Just%20press%20the%20%22Refresh%22%20button%20after%20you%20added%20new%20items.%20They%20will%20be%20added%20automatically%20to%20the%20summary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20look%20at%20the%20attached%20workbook%20and%20see%20if%20this%20makes%20sense%20to%20you.%20If%20not%2C%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934100%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Unique%20Values%20in%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EThank%20you%20very%20much!%20Let%20me%20give%20it%20a%20go%20and%20I%20will%20shout%20if%20there%20are%20any%20Issues.%20Have%20a%20Great%20Day.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-936636%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Unique%20Values%20in%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-936636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430924%22%20target%3D%22_blank%22%3E%40MCI-IT%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20the%20Pivot%20Table%20solution%20will%20definitely%20solve%20your%20immediate%20need.%20And%20it%20could%20continue%20to%20work.%20But%20for%20longer%20term%2C%20deeper%20resolution%20here%2C%20it%20might%20help%20if%20you%20could%20describe%20the%20bigger%20picture--what%20this%20column%20is%20part%20of.%20Not%20to%20pry%20into%20what%20might%20be%20proprietary%20matters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20can't%20imagine%20(despite%20the%20example%20you%20give)%20that%20this%20is%20about%20tracking%20the%20ingredients%20for%20fruit%20salad.%26nbsp%3B%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-941447%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Unique%20Values%20in%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-941447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430924%22%20target%3D%22_blank%22%3E%40MCI-IT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIf%20you%20enter%20the%20values%20to%20count%20in%20Column%20A%3C%2FP%3E%3CP%3EThen%20in%20Column%20C%2C%20create%20the%20following%20function%20in%20C2%20and%20copy%20it%20down%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DA1%26amp%3B%22%20%3D%20%22%26amp%3BCOUNTIF(%24A%241%3A%24A%249%2CA1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EThen%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ETo%20remove%20duplicates%2C%20select%20the%20functions%20in%20column%20C%20(C1%3AC9)%20%26gt%3B%26gt%3B%20Data%20Tab%20%26gt%3B%26gt%3B%20remove%20duplicates%20%26gt%3B%26gt%3B%20OK%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20441px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F145818iDF3A36BB6E141F8A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Apple%20countif.png%22%20title%3D%22Apple%20countif.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
MCI-IT
New Contributor

Morning All,

 

I'm stuck with a Problem that I'm not sure which formula will sort out, I got a Column with unique values in, now there are hundreds of these values and I don't know all of their names or how big the column will grow and I don't just want the quantity of the unique values but also the name's.

So, let’s say we got:

Apple

Pear

Orange

Banana

Grape

Orange

Apple

Banana

Orange

 

So, I need a Formula that will tell me the value name and quantity:

 

Apple = 2

Pear = 1

Orange = 3

Banana = 2

Grape = 1

 

As I said I don’t know all the Unique Values so the Formula will have to pick them up from the column.

 

I really hope there is a way of doing this else I will have to try and make a Separate Formula for Each Unique Value by going thru all the data and writing all the names down.

 

Appreciate any Help Greatly.

 

Have a Great Day.

 

 

 

 

 

4 Replies

@MCI-IT 

Create a pivot table where the data source refers to the entire column where your data sits. That way, you can keep on adding items to your list without having to update a static range every time you do so. Just press the "Refresh" button after you added new items. They will be added automatically to the summary.

 

Have a look at the attached workbook and see if this makes sense to you. If not, let me know.

 

 

@Riny_van_EekelenThank you very much! Let me give it a go and I will shout if there are any Issues. Have a Great Day.

@MCI-IT   the Pivot Table solution will definitely solve your immediate need. And it could continue to work. But for longer term, deeper resolution here, it might help if you could describe the bigger picture--what this column is part of. Not to pry into what might be proprietary matters.

 

But I can't imagine (despite the example you give) that this is about tracking the ingredients for fruit salad.  

 

 

@MCI-IT 

Hi

If you enter the values to count in Column A

Then in Column C, create the following function in C2 and copy it down:

=A1&" = "&COUNTIF($A$1:$A$9,A1)

Then 

To remove duplicates, select the functions in column C (C1:C9) >> Data Tab >> remove duplicates >> OK

 
 
 

Apple countif.png

 

Hope that helps

Nabil Mourad

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies