Apr 02 2021 12:09 PM
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!
Apr 02 2021 12:13 PM
Why don't you use Excel tables Overview of Excel tables - Office Support (microsoft.com) ?
Apr 02 2021 12:16 PM
SolutionLet'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)))
Apr 02 2021 12:20 PM
Apr 02 2021 12:22 PM
Apr 02 2021 12:49 PM
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...
Apr 02 2021 12:16 PM
SolutionLet'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)))