Top N Customers and Others

The only way I could do this in Pivot Table required some manual work.

1.) Add another item in the source table "All others" with this formula array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=SUM(B3:B370)-SUM(LARGE(IF(FREQUENCY(SUMIF(A3:A370,IF(MATCH(A3:A370,A3:A370,0)=

ROW(A2:A370)-MIN(ROW(A2:A370))+1,A3:A370),B3:B370),SUMIF(A3:A370,A3:A370,B3:B370)),

SUMIF(A3:A370,A3:A370,B3:B370)),{1,2,3,4,5}))

2.) Change the Data Source to include the additional item.

3.) Clear the existing filter (Top 5) from "Before" PT.

4.) Sort 'Sum of Amount' descending.

5.) Manually filter the first 5 (or N) items in 'Row Labels' and include "All others".

See the attached.