Forum Discussion
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. But then it becomes incorrect when I filter the count column from largest to smallest.
Thank you,
Amy
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.
2 Replies
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
