Forum Discussion
Solved
Why does Countifs formula changes when excel filtered?
Hello, May I ask how should I fix the excel formula so the formula doesn't change when I filter the excel table? Kindly see attached for an example. The top table numbers are correct. B...
AmyYang Use only structured references inside the table. Change the formula to
=COUNTIFS(Table1[Relevance Group],"1",Table1[Countries],[@Country])
The @ sign combined by the column name fixes the reference to the Country on the same row. If you don't do that, the direct reference will follow the the cell when it moves after a sort, as shown in the picture.
AmyYang Use only structured references inside the table. Change the formula to
=COUNTIFS(Table1[Relevance Group],"1",Table1[Countries],[@Country])
The @ sign combined by the column name fixes the reference to the Country on the same row. If you don't do that, the direct reference will follow the the cell when it moves after a sort, as shown in the picture.
Thank you so much Riny for your prompt and helpful feedback! The issue is now resolved with this solution, thanks!
Regards,
Amy
Regards,
Amy