"Other" category in Excel

[Guest]

I work for a translation company and work a lot in pivot tables to display
our top spend by target language (German, French, etc.). I would like to use
a pivot table that displays my top 10 languages with an "OTHER LANGUAGES"
category with the spend for all the remaining languages. If I filter the top
10 languages in the pivot table, the others disappear completely from the
report. So I have one unfiltered pivot table, and another table that
references the 1st with an "OTHER LANGUAGES" category that I created
manually. But is there any way to create an "OTHER" category automatically
within the pivot table?

 

[Guest]

not within the pivot table, but in your data set use a helper column
=if(top-ten-crteria, language-cell,"Other")
use this in your pivot table

 

[Guest]

Thanks a lot!
What formula would you use to define the top 10 criteria within the base
data set? Let's say I only have 2 columns. Column A contains target
languages, and column be an amount in USD.
Target Language Spend
French $1,000
Italian $,1500
Italian $10,000
Italian $5,000
French $1,000

Thanks again!

 

[Guest]

if you want the number of times the language was usewd, you could add a
helper column
with =countif(A:A,A1)
and look for the languages with the largest numbers
if you wanted the most dollars
in the helper column
=sumif(A:A,A1,B:B)

I would then use data-filter-advanced filter-unique to get a new table with
just line for each language

of you could use the filter to get the unique list before the countif or
sumif equations.

the criteria for top ten must be established by you.

 

[Guest]

Thanks again! I used =sumif(A:A,A1,B:B). It calculates the total spend for
each language.
So I have something like this (I have one line per purchase order):
French $7,442.73
French $7,442.73
French $7,442.73
French $7,442.73
French $7,442.73
French $7,442.73
French $7,442.73
Afrikaans $1,785.40
Afrikaans $1,785.40

I can't use the LARGE (array,{1,2,3,4,5,6,7,8,9,10]) function as it shows
the same largest amount 10 times. How would you display the top 10 amounts
but with each total above counted only once?

Thanks a mil...