Count Unique Values with Multiple Criteria Over Ranges

[QBERT]

I am trying to count unique values over a large range of data with multiple criteria but keep having to resort to manually counting as I cannot get the formula to work. I am going to post a picture of my table with the correct answers and then show the formula I am trying. Thanks.

For the Formula Try cell I typed the following:

=COUNTA(UNIQUE(FILTER('player_predictive - 1.28.2022'!$A$2:$A$76771,('player_predictive - 1.28.2022'!$AE$2:$AE$76771<=170)*('player_predictive - 1.28.2022'!$D$2:$D$76771="WR"))))

Player Predictive: is the name of my main data matrix
Column A: Contains player IDs, some repeat per incident; hence the need to count unique player IDS within an age and weight range
Column AE: Weights of players
Column D: Positions of Players entered in as Text in two and three letter WR, LB, OLB, etc.

The above formula returns 1 which counta does when there is not an answer. When I pull a pivot table and count manually I know the right answer is 21. Once I solve this I will then have to do the same over the range of weights (i.e. for players that weigh greater than 171 and or <= 180, etc.) I am looking to construct a formula that covers my entire table as I have many more table to make. Thanks.

Q

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.