This array is configured as a divisor with 1 as the numerator. After division, we get another array:. Any values that occur in just once in the range will appear as 1s, but values that occur multiple times will appear as fractional values that correspond to the multiple.
By concatenating an empty string "" to the data, we prevent zeros from ending up in the array created by COUNTIF when there are blank cells in the data. It works because using an empty string "" for criteria will count empty cells.
If you want to exclude blank cells from the count, use:. This has the effect of canceling out the count of blank cells by making the numerator zero for associated counts. Here's a formula for numeric values , and one for text values. Formulas are the key to getting things done in Excel. You'll also learn how to troubleshoot, trace errors, and fix problems.
Instant access. See details here. The returning values are then summed if they are not 0. This gives you a count for the distinct values, regardless of their types. Does the same thing as the previous formula. The frequency function returns the number of occurrences for a value for the first occurrence. For the next occurrence of that value, it returns 0.
Counts the number of distinct numeric values. Returns the count of distinct text values in a range. Like the first example, this counts the distinct values, but the ISTEXT function makes sure only the text values are taken into count.
You can also count distinct values in Excel using a pivot table. To find the distinct count of the bike names from the previous example:. Still need some help with Excel formatting or have other questions about Excel?
Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. By continuing to use this website, you agree to their use. Connect anytime to free, instant, live Expert help by installing the Chrome extension Add Excelchat to Chrome. Quick Excel Help. Get instant live expert help with Excel or Google Sheets. Post your problem and you'll get expert help in seconds Your message must be at least 40 characters.
Our professional experts are available now. Your privacy is guaranteed. Connect to an Expert. How to Count Unique and Distinct Values in Excel Working with large data sets, we often require the count of unique and distinct values in Excel. How to Count Unique and Distinct Values in Excel The unique values are the ones that appear only once in the list, without any duplications.
To count the distinct values from the previous example: Select the range of cells A1:A In the Advanced Filter dialog box, click Copy to another location. Keep the Unique Records Only box checked. Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you!
Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback?
0コメント