Forum Discussion
Solved
Using the COUNTIF Function to Find Unique Values
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 formu...
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)))
Why don't you use Excel tables Overview of Excel tables - Office Support (microsoft.com) ?
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...