Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single row or a single column using an array formula.

The example below shows the operation on a one-dimensional array with no repeat statistics.

Match Single Criteria in Excel

Buy some gifts to reward the top defect submitters for their contributions to the company. The core is to count how many submitters appear on this list with repeated submitters ignored.

MATCH Function

Build a formula with EXCEL COUNT and MATCH functions:

=COUNT(1/(MATCH(C2:C7,C:C,0)=ROW(C2:C7)))
Match Single Criteria in Excel

It is an array formula, so press Shift+Control+Enter to run it.

In this formula:

  • The “Help Column” shows the logical values returned by the MATCH function.
  • The MATCH function determines whether the submitter appears in the list for the first time. It returns logical TRUE if it detects that the submitter does not appear in the range from the first cell to the cell above it, or logical FALSE if the submitter already appears in the searched field.
Match Single Criteria in Excel

Use “1” divided by the logical value array to get a new array {1;1;#DIV/0!;1;1;#DIV/0!}.

Match Single Criteria in Excel

The COUNT function counts the numbers with errors ignored.

COUNTIF Function

We can also use EXCEL SUM and COUNTIF functions to count without repeats.

Build a formula with EXCEL SUM and COUNTIF functions:

=SUM(1/COUNTIF(C2:C7,C2:C7))
Match Single Criteria in Excel

It is still an array formula, press Shift+Control+Enter to run it.

In this formula:

  • COUNTIF(A:A,A:A) is a classic usage in statistics to count the number of occurrences of each value in column A.
  • COUNTIF (C2:C7,C2:C7) returns an array that stores the number of occurrences of each submitter in the list. The array is {3;1;3;1;1;3}.
Match Single Criteria in Excel
  • Use “1” divided by the array returned by COUNTIF function to get a new array {1/3;1;1/3;1;1;1/3}.
Match Single Criteria in Excel
  • The SUM function adds up the numbers in the array. For duplicate values, for example, Andy in the example, 1/3+1/3+1/3=1, so there are no duplicates in the count, which completes the statistics without duplicates.
Related Posts

Excel Array Formula

An array formula is a special formula that is converted from a normal formula by the Ctrl+Shift+Enter shortcut. Excel automatically adds curly brackets "{}" at the beginning and end of an array formula. The essence of the array formula is ...

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to ...

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Free Family Budget Template (Step-by-step Guide)

This article will show you some free printable family budget templates for google sheet and Microsoft Excel spreadsheet applications and will also explain some of the features or important functions of these templates. This will make it easy for you ...

Comments

So empty here ... leave a comment!

Leave a Reply

Sidebar