Counts the number of distinct values in a column.
Aug 30, 2017 Excel Distinct Count or Unique Count. Pivot tables will offer a distinct count, if you check one tiny box as you create the pivot table. Here is an annoyance with pivot tables. Drag the Customer column from the Data table to the VALUES area. I'm looking for a formula calculating: distinct Count + multiple criteria Countifs does it but do not includes distinct count. Here is an example. I have a table on which I want to count the number of distinct items (column item) satisfying multiple conditions one column A and B: A2 and B. To get unique number of rows from the 'orders' table with following conditions. Only unique custcode will be counted, 2. Result will appear with the heading 'Number of employees', the following SQL statement can be used: SELECT COUNT ( DISTINCT custcode ) AS 'Number of employees' FROM orders; Sample table: orders. Details: Count unique values in Excel You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM (IF (1/COUNTIF (data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears. Excel formula distinct count.
Syntax
Parameters
| Term | Description |
|---|---|
| column | The column that contains the values to be counted |
Return value
The number of distinct values in column.
Remarks
The only argument allowed to this function is a column. You can use columns containing any type of data. When the function finds no rows to count, it returns a BLANK, otherwise it returns the count of distinct values.
DISTINCTCOUNT function includes the BLANK value. To skip the BLANK value, use the DISTINCTCOUNTNOBLANK function.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Example
The following example shows how to count the number of distinct sales orders in the column ResellerSales_USD[SalesOrderNumber].
Using the above measure in a table with calendar year in the side and product category on top returns the following results:
| Row Labels | Accessories | Bikes | Clothing | Components | - | Grand Total |
|---|---|---|---|---|---|---|
| 2005 | 135 | 345 | 242 | 205 | 366 | |
| 2006 | 356 | 850 | 644 | 702 | 1015 | |
| 2007 | 531 | 1234 | 963 | 1138 | 1521 | |
| 2008 | 293 | 724 | 561 | 601 | 894 | |
| 1 | 1 | |||||
| Grand Total | 1315 | 3153 | 2410 | 2646 | 1 | 3797 |
In the above example, note that the rows Grand Total numbers do not add up, this happens because the same order might contain line items, in the same order, from different product categories.
See also
COUNT function
COUNTA function
COUNTAX function
COUNTX function
Statistical functions
Count All Distinct Values in a Range in Excel:
In this article, we are going to learn how to Count All Distinct Values in a Range in Excel 365 with Kutools.
Use Distinct Count In Excel Formula
Kutools is a powerful toolbox, it has many predefined functions and features to make users work easier. We can easily perform complicated calculations in Excel with the help of it.
How To Use Distinct Count In Excel 2010
Jump To:
Steps to Count All Distinct Values in a Range in Excel:
- To Count All Distinct Values in a Range in Excel, do as follows.
- For example, enter the names in a Column. In another column, enter the names (first duplicate values)that you want to count.
- Select the Blank Cell to get the Output.
- On the Kutools tab, Select Formula Helper icon, it will display the menu.
- From that menu Select Formula Helper option.
- It will open the Formula Helper Dialog box. In that Dialog box, you have to select the Count cells with unique values (include the first duplicate) option from Choose formula box.
- In the Range box, Select the range of cells in which you want to count the distinct values.
- Hit the Ok button.
- Now, it will display the distinct count in the output box.
From the above image, you can see the distinct count of the range of cells displayed properly.
Verdict:
To Sum-up, I hope this article can help you to understand the steps to Count All DistinctValues in a Range in Excel 365 using Kutools. Give your feedback in the below comment section. Thanks for visiting Geek Excel. Keep Learning!!
