I need to count how many different values there are ( it should be 10 different values). I got the answer by using SUMPRODUCT(1/COUNTIF(A2:A37,A2:37)), however, I don't understand the formula, can someone please help me explain? If I do the COUNTIF separately, the result is 0? How does SUMPRODUCT(1/COUNTIF) help? Also, inside the COUNTIF, the range and criteria are the same, what does this mean? I understand that the range is where we look for, and the criteria is for what criteria, but if the criteria is the entire range, how are we specifying what we're looking for here? How does this translate/work?
CodePudding user response：
Just extend some of the comments provided in your question for a better understanding. Here: Excel dynamic arrays, functions and formulas you can find a detail explanation about dynamic arrays and implicit interception and how it has changed through Excel evolution.
SUMPRODUCT (array1, [array2], ...) can be used with a single argument, in such case it sums all elements in array1, i.e. similar to SUM function (Excel SUMPRODUCT Function). For example: SUMPRODUCT({1;2;3}) = 6.
Note: Multiplication is the default operation of SUMPRODUCT, but you can use others (check this link) replacing the , with the corresponding operation. For example: SUMPRODUCT({1;2;3}/({1;2;3}))=3 (acts as COUNT), i.e. 1/1 2/2 3/3 but SUMPRODUCT({1;2;3},{1;2;3})=14, i.e. 1*1 2*2 3*3.
COUNTIF(range, criteria) the first argument has to be a range, but the second argument can be a number, expression, cell reference, or text string that determines which cells will be counted, but it also can be a range or an array (Excel COUNTIF function). If that is the case, COUNTIF is invoked for each element of criteria and returns an array of the same size and shape as criteria. For example if the following range A1:A4 has the following values:
|1|
|2|
|1|
|1|
the following expressions will return:
COUNTIF(A1:A4, 1) = 3
COUNTIF(A1:A4, A1:A2) = {3;1} i.e. 2x1 array
COUNTIF(A1:A4, {1;2}) = {3;1}
COUNTIF(A1:A4, {1,2}) = {3,1} i.e. 1x2 array
Note: Remember you can not invoke the function with the first input argument as an array, for example: COUNTIF({1;2;1;1}, 1) returns an error. It has to be a range.
so we can count how many times each element of the first input argument is repeated like this:
COUNTIF(A1:A4,A1:A4) = {3;1;3;3} i.e. 4x1 array
Now back to SUMPRODUCT (remember with a single argument it is just the sum of the elements):
SUMPRODUCT(COUNTIF(A1:A4,A1:A4))
= SUMPRODUCT({3;1;3;3})
= 10
i.e. sum of occurrences of each element of A1:A4
and finally:
SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))
= SUMPRODUCT({1/3;1/1;1/3;1/3})
= 3*(1/3) 1
= 2
which result in total total number of unique elements in A1:A4. As @Harun24hr pointed out, it can be achieved in Microsoft 365 with: COUNTA(UNIQUE(A1:A4) or also COUNT(UNIQUE(A1:A4) since in the example of this answer A1:A4 are numbers, but in the sample of your question (letters) you have to use: COUNTA.