There is no function that calculates the distinct count for a range of values in MS Excel.
However by using a combination of functions the Distinct Count can be calculated. In the example below by using a combination of the SUM, IF and FREQUENCY functions we are going to calculate the total number of clients who have placed orders for various Products.
FUNCTION DETAILS
The FREQUENCY function calculates how often values occur within a range of values, and then returns a vertical array of numbers. Because this function returns an array, it must be entered as an array formula.
The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.
The SUM function adds all the numbers that you specify as arguments.
Applies To: MS Excel 2003, 2007 and 2010
1. To calculate the distinct number of clients who have placed orders
2. Select cell B18 and enter the following formula
=SUM(IF(FREQUENCY(B2:B17,B2:B17)>0,1))
3. The answer will be 10. That is the count of unique values in the range B2:B17. This formula ignores blank cells and applies to numeric values
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email [email protected] and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
