COUNTIF/COUNT Function help required to count the filtered data for the subsequent columns

Question

I am using a normal COUNTIF Function to know the count of "Box 9" I have placed this in one cell "I12" and i am using the formula.

I am filtering the data with the column J.

1)

=COUNTIF($X$36:$X$16837,I12) [This formula is in J13]

X36 - X16837 will contain the Box 9, Box 8, Box 7 and so on till Box 1

2)

=COUNTIF($X$36:$X$16837,I12)/COUNT($B$36:$B$16837) [this formula is in I13]

My Requirement is whatever the filter in Column J i want to get the above functions work when i filter mode. It should show the result of the above formulas

Is this possible?

Answer 1

I could find a solution only with helper column:

In column Y enter: =Aggregate(3,5,X36), this will result 1 for visible rows and 0 for hidden ones (you'll see only 1s, but don't worry, it works).

Formula to J13: =SUMIF($X$36:$X$16837,I12,$Y$36:$Y$16837)

Formula to I13: =J13/SUM($B$36:$B$16837)