I want to count the total number of distinct properties (Property column) in the HZ locations (just the ones that have the text “hz” in any part of their name in the “UWI” column) for a table of data. But I don’t seem to get the correct function to work even for this small table where I can check visually if it’s working, and I have to do this task for other tables with way more data.

I would like to do this with a function because the empty cells of some columns are being count with the COUNTA() function and even using a Pivot table, and I also have trouble replacing those empty cells, I keep getting the message: “We couldn’t find what you were looking for” when I leave in blank the “Find what:” option of the find & select tab (the file is unprotected).

The function that I’ve been trying and runs but not gets the right answer is this one: =COUNTA(UNIQUE(FILTER(Table36[Property],Table36[UWI],”hz“)))

The result I get is 1, which could be right, BUT, if I change the data on the property column in any other row without “hz” on its name (UWI column), the result changes when it shouldn’t.

Table

Note: I’m a basic or barely intermediate user of Excel.