Count Unique Invoices Using Frequency Function or COUNTIF

In today's digital era, Excel has become an indispensable tool for countless individuals and businesses who rely on it for organizing and analyzing massive amounts of data. One often-underutilized feature of this powerful software is the Frequency array function. While many are familiar with other array functions like Offset, and Index, they can bring tremendous benefits when used within an Excel workbook too. The frequency function is one of the most powerful functions, that unravel the mystery of the complex numerical array problems.

Features of Frequency Function

• The Frequency function works with the numbers only and contains two arguments named data_array and bins_array in the function definition. Upper limits should be properly defined in the second argument.

• It is apparent and fastest to retrieve the result.

• It returns a zero count for the duplicate values that exist in a certain column.

• It is efficient to work with a vertical array constant but also work with a horizontal function along with a transpose function.

• Frequency function omits cells that are empty.

Count Unique Invoice Numbers by Using the Frequency Function

• Step 1 To retrieve the non-duplicate invoice numbers, consider the dataset as shown in the below image.

• Step 2 Enter the formula in the G3 cell =SUMPRODUCT(--(FREQUENCY(B2:B11,B2:B11)>0)). Now press the ctrl+shift+Enter key. The resultant array would be assumed as an individual unit. The frequency function is used inside the SUMPRODUCT function as highlighted in below image

• Step 3 The resultant value is displayed in the G3 cell as shown below

By using the COUNTIF Function

• Step 1 Assume the same dataset to accomplish the same task by using the COUNTIF function.

• Step 2 Write the formula =SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11)) in the G4 cell as illustrated in the below image

• Step 3 After writing the formula, press ctrl + Shift + Enter in the G4 cells to count the number of the unique invoice number.

Conclusion

In this article, the main goal of the Frequency function/COUNTIF is to count the number of unique invoice numbers defined in the specified column B. It would also utilize complicated array formulas and work only with the vertical array. If duplicate values exist in the dataset, then the frequency function returns the zero count. For the usage of the horizontal array, the transpose function would be defined along with the frequency function.