Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to count the number of unique invoices by using the 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.
164 Views
