Google Sheets Formula for Keyword Density in Cells

Today, walking through my local salvation army, I saw that coffee mugs were 2 for $1. “Holy shit”, I thought, “I can make some money off that”. I’ve learned to not buy stuff purely on instinct of emotion (thanks to the 500 oak bannister fiasco of 2010) so, when I got home, I scraped the last 200 ebay sold mug listings (basically just the weekends) and, beyond thinking, “DAMN, people fuckin love mugs”, I realized that, yes, there def is a lot of profit to be made sourcing mugs at 50 cents. The most interesting things I found were that 1. avg mug price was approx $17.50 including shipping and 2. 10% of all mug sales were Christmas themed.

google sheets ebay keyword density

How did I find the keyword density? W/this formula

To return the top 10 individual words in column B, with their frequency, try:

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(" ";A2:A);" ")&{"";""});"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 50 label Col1 'Word', count(Col2) 'Frequency'";0))


Just copy + paste it in a blank column and presto chango- you’ve got a cheat sheet for what items to look for at your next thrift store sourcing haul.

About the author

Walter Blake Knoblock is a lot of things. Follow him on twitter @WBKnoblock and on