This example shows how to create an array formula that will count unique values.
- Let’s use the function COUNTIF (COUNTIF). For example, let’s count the number of numbers 5 in the range A1: A6using the following formula:
=COUNTIF(A1:A6,5)=СЧЁТЕСЛИ(A1:A6;5) - To count unique values, add a function SUM (SUM), 1/, and replace 5 with A1: A6.
=SUM(1/COUNTIF(A1:A6,A1:A6))=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6))
- Finish entering the formula by pressing Ctrl + Shift + Enter.
Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.
Explanation:
- Range (array of constants) created with a function COUNTIF (COUNTIF) is stored in Excel memory, not in worksheet cells.
- The array of constants looks like this: {3;1;1;1;3;3} which means: three 7s, one sun, one moon, one 5, three 7s, three 7s .
- Все это сводится к {1/3;1/1;1/1;1/1;1/3;1/3}.
- This array of constants is used as an argument to the function SUM (SUM), giving a result of 4.