May 11 2022 06:08 PM
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
May 11 2022 08:36 PM
Solution@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.
May 11 2022 09:05 PM
May 11 2022 08:36 PM
Solution@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.