I used whole column reference (both absolute way and relative way), why my formula result still change after filtering that column

[michellelyxs]

Hi guys,

I used a formula to count "TY2020" in column C, with relative reference as C:C and absolute reference $C$1:$C$1000; however, both methods will give me 0 if I filter out "TY2020".

Is there any way I can still count out the occurrence of "TY2020" even if it is filtered out?

Thanks so much!

 

[Legacy 143009]

I am not sure İf it is possible in pivot table since pivot filter updates row values rather than hiding them.
Try to count original datasource.

 

[michellelyxs]

I am not sure İf it is possible in pivot table since pivot filter updates row values rather than hiding them.
Try to count original datasource.

Thank you for your thoughts! However this doesn't happen to another pivot table of this workbook:

I filtered out only 2 rows of FL here but it can count the whole column giving me 15. This is so weird that in the same workbook countif is working in different ways...

 

[Legacy 143009]

It is not a pivot table.

 

[michellelyxs]

It is not a pivot table.

It is - when clicking the column the pivot table analysis menu pops up.

 

[Legacy 143009]

No, it is just a table. Maybe it is a power pivot. I am not familiar with them. I don't know.
The only thing I know is, one is filtering with hiding rows which allows you to count hidden rows.
The other one is updating the rows, which makes the data unreachable for the function.