Hello Friends Its Sachin Here with another wonderful blog on counting unique value with the help of an array formula.

the need of this blog arise when i was working with one GST Template and there was one column heading named number of invoice which will always show you total number of unique number of invoices. On further analyzing it i finally found the answer.

Wanna Know How ???

then Lets get started guys

Finding Count of Unique invoice values

Explanation – In the above image you can see that total number of invoice are 14 however the total number of unique invoice counted above by formula are 9.

This is with the help of an array formula used over there

Formula – ={SUM(1/COUNTIF($B$9:$B$22,$B$9:$B$22))}

Explanation of the formula – {Sum(1/Countif(Range,Criteria)}

Now lets see how does this formula work

The above formula is the combination of SUM formula + Count If Formula + Array formula on them

Steps in How this formula works

As this is an array formula we have have to hit Control+Shift+Enter After entering the formula in cell.

If you want to know how COUNT IF work click here

Explanation of Steps of Formula

1.) Enter the Given Formula

2.) As this is the Array Formula which means each criteria will be evaluated against the given range of COUNT IF and as a result we will get multiple result from count if formula i:e 1,1,2,2,3,3,1,1,3,1,2,2,2,2. This is the benefit of array formulas Excel.

the results shows the number of times each invoice is repeating in an range.

3.) Now all the result of Count if we got from second step will be divided from 1 and as a result we will again get multiple result.

for ex – a invoice is repeating once when divided upon 1 will result in 1 whereas a invoice repeating 2 times on dividing upon 1 will result in 0.5 and same when a invoice repeats thrice will give us 0.333

It means Sum of count of an invoice repeating twice will be 1 (0.5+0.5) and sum of count of an invoice repeating thrice will also given us 1 (0.33+0.33+0.33)

and thus we will get sum of count of only unique values by applying this formula

Things to be taken care of while applying this formula.

1.) DO not just enter after applying the formula hit Control+Shift+Enter after entering formula because this is an array formula

2.) As this is an array formula do not drag it after entering it.

Conclusion

Apart from using the formula for counting unique invoice number we can can use it in carious other data as per our requirement.

in case of any other query you can contact me via mail at Chetnanisachin@gmail.com

Thank you so much for giving your precious time.

Leave a Reply

Your email address will not be published. Required fields are marked *