SOLVED

Using the COUNTIF Function to Find Unique Values

Copper Contributor

Hello!  I have an Excel workbook with 100+ tabs, each containing a data table that starts in A12 (headers in row 11) and spans across to column Z.  In cell A1 of each tab, I'd like to include a formula that counts all unique values of data in column H in that specific tab.  Currently, I am using the following:

 

=SUMPRODUCT(1/COUNTIF(H12:H14,H12:H14))

 

Data begins in row 12 of each tab.  There can be as few as one row of data in the tab (going to H12) to as many as 80,000 rows.  This data is updated regularly, which causes the number of rows to change with each update.  I am attempting to create a formula that can be copied to all 100+ tabs without the need to change the row number in each tab after each update.  (i.e., I currently have to change "H14" to the last row of each tab.  Way too time-consuming.)  I replaced H12:H14 with H:H, but no luck.  

 

Seeking advice from this group to come up with a solution.  

 

Thank you!

5 Replies
best response confirmed by mucrick (Copper Contributor)
Solution

@mucrick 

Let's say that the data in none of your sheets extends below row 100000.

As an array formula confirmed with Ctrl+Shift+Enter:

 

=SUM(IF(H12:H100000<>"",1/COUNTIF(H12:H100000,H12:H100000)))

That semed to solve the issue! Thank you for the quick response and solution!
Thank you, Sergi, for the quick response. Data Tables are being used. I ended up using the solution provided below.

@Sergei Baklan 

A formula from a cell outside a table referring to a table column using structured table refeferences has to include the table name.

Table names have to be unique across the workbook, so you couldn't simply use the same formula in each sheet.

It would be possible to create dynamic named ranges with the same name on each sheet, but that would be extra work...

1 best response

Accepted Solutions
best response confirmed by mucrick (Copper Contributor)
Solution

@mucrick 

Let's say that the data in none of your sheets extends below row 100000.

As an array formula confirmed with Ctrl+Shift+Enter:

 

=SUM(IF(H12:H100000<>"",1/COUNTIF(H12:H100000,H12:H100000)))

View solution in original post