Read these articles for more details on CountIF Function or SumProduct Function. This means that, if a value appears 4 times in the range then its value will become as 1/4 = 0.25 and value that is present only once will become 1/1 = 1įinally, the SumProduct formula adds up all the elements in the array and returns the final result. This modifies the result of CountIF formula such that values that only appear once in the array become 1 and the numbers corresponding to duplicate values become fractions corresponding to the multiple. Next, the results of CountIF formula are used as a divisor with 1 as numerator. The result is an array of numbers and in our example it might look something like this. The CountIF function looks inside the data range (B2:B9) and counts the number of times each value appears in data range. Let’s first understand the role of inner CountIF function.
This formula consists of two functions combined together. ‘data’ – data represents the range that contains the values
