This article will show you how to create an array formula that counts unique values in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to count unique values in Excel
1. We use the COUNTIF function. For example, to count the number 5, use the following function.
2. To count unique values, we add the SUM function, 1/ and replace 5 with A1:A6.
3. Finish by pressing CTRL + SHIFT + ENTER.
Note: The formula bar indicates this is an array formula by enclosing it in curly braces {}. Don’t type these yourself. They will disappear when you edit the formula.
Explanation: The range (array constant) created by the COUNTIF function is stored in Excel’s memory, not in a range. The array constant looks like this:
{3;1;1;1;3;3} – (three numbers 7, 1 sun, 1 moon, one 5, three numbers 7, three numbers 7)
Reduces to: {1/3;1/1;1/1;1/1;1/3;1/3}
4. The array formula below counts the number of values that appear exactly once.
Explanation: the IF function converts the array constant {3; first; first; first; 3; 3} to {0; first; first; first; 0; 0}. This array constant is used as a parameter to the SUM function, which returns 3.
5. If you are using Excel 365, simply use the UNIQUE function to extract the unique values.
6. Next, add the COUNTA function to count unique values.
7. The formula below counts the number of values that appear exactly once.
Note: the UNIQUE function has 2 optional parameters. The default value is 0 (second parameter) for the UNIQUE function to extract values from a vertical array. The value 1 (the third parameter) tells the UNIQUE function to extract values that appear exactly once.
In addition, you can also view many other excel articles here.
