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

[ajayxlnc2]

Hi

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?

 

[BBD]

Hi Ajay,

It will really be helpful if you can attach a sample file.

Thanks,
BD

 

[ajayxlnc2]

Hi BD,

Sample file attached.

 

[BBD]

Hi Ajay,

The count/countif function does not exclude the filtered out rows by itself. And I believe there is no inbuilt function to bypass it. You can do this by creating a dynamic named range and have your base data sorted in a specific manner.

However, here is how I would do this. I would create an UDF to track if the row height is greater than 0 (tells me if the data is filtered out) and then use this column to be part of the countifs formula. In the attached file below, I have created the UDF and inserted the formula in column AC. I have modified the formulas in cell in E13 and E14 to consider the UDF. See how the value changes when you click Group4 and Showall.

Couple of tips about the formulas you have used :
1. Countif does not result in an error if there is no matching data. So its not really required to use is error formulas.
2. The alternative to If and iserror combination is iferror which is much simpler and has lesser arguments.

Thanks,
BD