SOLVED

Why does Countifs formula changes when excel filtered?

%3CLINGO-SUB%20id%3D%22lingo-sub-3361808%22%20slang%3D%22en-US%22%3EWhy%20does%20Countifs%20formula%20changes%20when%20excel%20filtered%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3361808%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20I%20ask%20how%20should%20I%20fix%20the%20excel%20formula%20so%20the%20formula%20doesn't%20change%20when%20I%20filter%20the%20excel%20table%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20see%20attached%20for%20an%20example.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20top%20table%20numbers%20are%20correct.%20But%20then%20it%20becomes%20incorrect%20when%20I%20filter%20the%20count%20column%20from%20largest%20to%20smallest.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AmyYang_0-1652317669925.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371006i16BD77FF960CB706%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AmyYang_0-1652317669925.png%22%20alt%3D%22AmyYang_0-1652317669925.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EAmy%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3361808%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3362074%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20Countifs%20formula%20changes%20when%20excel%20filtered%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3362074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3BUse%20only%20structured%20references%20inside%20the%20table.%20Change%20the%20formula%20to%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Table1%5BRelevance%20Group%5D%2C%221%22%2CTable1%5BCountries%5D%2C%5B%3CSTRONG%3E%3CFONT%20color%3D%22%23DF0000%22%3E%40Country%5D%3C%2FFONT%3E%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%26nbsp%3B%40%20sign%20combined%20by%20the%20column%20name%20fixes%20the%20reference%20to%20the%20Country%20on%20the%20%3CU%3Esame%20row%3C%2FU%3E.%26nbsp%3BIf%20you%20don't%20do%20that%2C%20the%20direct%20reference%20will%20follow%20the%20the%20cell%20when%20it%20moves%20after%20a%20sort%2C%20as%20shown%20in%20the%20picture.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-12%20at%2005.30.26.png%22%20style%3D%22width%3A%20235px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371021i5829F229C2A26678%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-12%20at%2005.30.26.png%22%20alt%3D%22Screenshot%202022-05-12%20at%2005.30.26.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3362142%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20Countifs%20formula%20changes%20when%20excel%20filtered%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3362142%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20Riny%20for%20your%20prompt%20and%20helpful%20feedback!%20The%20issue%20is%20now%20resolved%20with%20this%20solution%2C%20thanks!%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3EAmy%3C%2FLINGO-BODY%3E
Contributor

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. 

 

AmyYang_0-1652317669925.png

 

Thank you,

Amy 

2 Replies
best response confirmed by AmyYang (Contributor)
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.

Screenshot 2022-05-12 at 05.30.26.png

Thank you so much Riny for your prompt and helpful feedback! The issue is now resolved with this solution, thanks!

Regards,
Amy