AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Count unique values excel12/11/2022 ![]() ![]() You can also check out our other informative & useful articles related to Excel functions on this website. If you think I’ve missed a method that should have been added to then please let me know in the comment section. I hope that all the major methods along with two alternatives should cover and function perfectly with your regular Excel works. ➥ Related: How to Count Unique Values in Excel Using Pivot Table Here, you’re seeing now the maximum profit from all sales of Lenovo & Asus notebooks. ➤ Similarly, select Notebook from Device Category options. ➤ From Filter options of Brand, select HP & Lenovo and then Press OK. ➤ Now go to your worksheet where you’ll find a filtered table. ➤ From the Value Field Settings dialogue box, choose Max from the scrollings under the ‘Summarize Value By’ bar. ➤ Click on the drop-down from the Profit field & select Value Field Settings. ➤ Similarly, tug the Profit header to the Values field. ➤ Drag Brand & Device Category headers to the Filters area with your mouse. ➤ Press OK & a new sidebar window named Pivot Table Fields will appear. ➤ Select Existing Worksheet & a specific cell for output data. ➤ From the Insert tab, select Pivot Table, a dialogue box like in the picture below will appear. ➤ Select the whole array of data (C5:E27). Here we’ll go for only one criterion & that is finding the maximum profit from Lenovo & Asus notebooks. Do you want to count on the unique number of rows within your Excel file I have created a Excel file (Only have one column in Excel table) on my OneDrive folder and the data structure of it as below: I have made a test on my side, please take a try with the following workaround: Add a proper trigger, here I use Flow Button trigger. ![]() With Pivot Table filtering, we can find out a wide range of output. It’ll count the sales of Lenovo & Asus notebooks with more than $100 profits from the table. ' Get the number of rows including the first row which is the header ORange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=oTarget, Unique:=True ' Copy the unique values from your oRange to the first unused cell on row 1 firstCell in range: It is the reference of the first cell in range. SUM (- ( FREQUENCY ( MATCH (range,range,0), ROW (firstcellinrange)+1)>0)) Range : The range from which you want to get unique values. Comparing the Two Solutions Both of these solutions are great because they can be refreshed when new data is added to the source table. Generic formula to count unique text values in excel. Set oTarget = (1, oTarget.Column + 1) ' the first unused cell on the first row The Distinct Count function goes through the Deal ID column and gives us a count of the unique values, so our summary report will look just like it did for Solution 1. Set oTarget = oRange.SpecialCells(xlLastCell) ' the last cell on the worksheet ' Get the first unused cell on the first row where the unique vaues will be temporarily populated ' in which case the header will be that value. ' You can ignore this element unless the first row in your oRange is a unique value ' Note: The index:0 element in the returned array will be the header row. ' and then populate and retuns an array of unique values ' Uses the built-in AdvancedFilter Excel command to return the unique values onto the Worksheet Public Function UniqueValues(oRange As Range) As Variant This uses the inbuilt AdvancedFilter command instead of cycling through each cell at a time. This might be a more efficient way of dealing with a large number of rows. In my tests, this one from Jacob above only works for numbers, not text, and does not handle errors in referenced cells (returns an error if any of the referenced cells returns an error): =SUM(IF(FREQUENCY(G4:G29,G4:G29)>0,1)) If you want errors completely excluded, it would need to be modified for that. For example, if you have two cells returning #DIV/0! and three cells returning #VALUE!, those 5 cells would add 2 to the final count of unique values. Note, the way it handles errors is by simply including them in the count of uniques. Using it in VBA, it apparently automatically handles the need to be an array formula. It's also one of the most compact solutions I've seen. It works for both numbers and text, it handles blank cells, it handles errors in referenced cells, and it works in VBA. (Ctrl+Shift+Enter, and don't include the curly brackets) After reading through this and then investigating further, I've got one that works better for me than anything I see here:
0 Comments
Read More
Leave a Reply. |