function - Count Unique Hashtags - Stack Overflow

admin管理员组

文章数量:1391925

On this table i have a column that features all hashtags i used per post, as for example, cell E2 has 30 hashtags, cell E3 another 30 hashtags and so on untill i reach cell E254. I want to count how many times a specific hashtag it appears on column HASHTAGS. What is the best Google Sheets function to apply this?

Example: E2 - #maisonvalentina #luxuryhouses #luxurybathroom #dreambathroom #bathtub #vanitycabinet #luxurymirror #inspiration #decorinspo #dailyinspo #london #londonliving #timelesselegance #dreambath #signaturedesign #moderndesign #timeout #archdaily #residential #resortdesign #wooden #marble #homesociety #homesocietystudio #luxuryhouses #homesocietycollection #homeinspirationideas #covetgroup #insplosion #luxxu

E3 - #maisonvalentina #luxurybathroom # #luxuryhomes #bathtub #sidetable #inspiration # #dailyinspo #unitedarabemirates #abudhabidesign #timelesselegance #dreambath #signaturedesign #moderndesign # #timeout #elledecor #residential #resortdesign #marblebathroom #marble #homesociety #homesocietystudio #homesocietycollection #homeinspirationideas #covetgroup #luxuryhomes #insplosion #brabbu

After delete duplicates i want to have a final count of how many times it shows: #maisonvalentina #luxurybathroom (and so on...)

Thank you in advance

I tried some functions like:

=SUM(ArrayFormula((LEN(E2:E255) - LEN(SUBSTITUTE(E2:E255, "#luxury", ""))) / LEN("#luxury")))

=ARRAYFORMULA(IF(U2:U="", "", SUM((LEN(E$2:E$255) - LEN(SUBSTITUTE(E$2:E$255, U2:U, ""))) / LEN(U2:U))))

But none of those worked

On this table i have a column that features all hashtags i used per post, as for example, cell E2 has 30 hashtags, cell E3 another 30 hashtags and so on untill i reach cell E254. I want to count how many times a specific hashtag it appears on column HASHTAGS. What is the best Google Sheets function to apply this?

Example: E2 - #maisonvalentina #luxuryhouses #luxurybathroom #dreambathroom #bathtub #vanitycabinet #luxurymirror #inspiration #decorinspo #dailyinspo #london #londonliving #timelesselegance #dreambath #signaturedesign #moderndesign #timeout #archdaily #residential #resortdesign #wooden #marble #homesociety #homesocietystudio #luxuryhouses #homesocietycollection #homeinspirationideas #covetgroup #insplosion #luxxu

E3 - #maisonvalentina #luxurybathroom # #luxuryhomes #bathtub #sidetable #inspiration # #dailyinspo #unitedarabemirates #abudhabidesign #timelesselegance #dreambath #signaturedesign #moderndesign # #timeout #elledecor #residential #resortdesign #marblebathroom #marble #homesociety #homesocietystudio #homesocietycollection #homeinspirationideas #covetgroup #luxuryhomes #insplosion #brabbu

After delete duplicates i want to have a final count of how many times it shows: #maisonvalentina #luxurybathroom (and so on...)

Thank you in advance

I tried some functions like:

=SUM(ArrayFormula((LEN(E2:E255) - LEN(SUBSTITUTE(E2:E255, "#luxury", ""))) / LEN("#luxury")))

=ARRAYFORMULA(IF(U2:U="", "", SUM((LEN(E$2:E$255) - LEN(SUBSTITUTE(E$2:E$255, U2:U, ""))) / LEN(U2:U))))

But none of those worked

• function
• google-sheets

Share Improve this question Follow edited Mar 13 at 2:02 Harun24hr 37.3k66 gold badges2323 silver badges4545 bronze badges asked Mar 13 at 0:09 Social Maison ValentinaSocial Maison Valentina 111 silver badge 2

• Please read the definitions of the tags you are using and explain why you chose to tag this with excel – Ron Rosenfeld Commented Mar 13 at 1:12
• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Mar 13 at 4:01

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Lets give a try to the following formula-

=QUERY(TOCOL(SPLIT(TEXTJOIN("#",1,TOCOL(E2:E,1)),"#"),1),"select Col1, count(Col1) where Col1 <>' ' group by Col1 label count(Col1) ''")

Output from above formula-

abudhabidesign	1
archdaily	1
bathtub	2
brabbu	1
covetgroup	2
dailyinspo	2
decorinspo	1
dreambath	2
dreambathroom	1
elledecor	1
homeinspirationideas	2
homesociety	2
homesocietycollection	2
homesocietystudio	2
inspiration	2
insplosion	2
london	1
londonliving	1
luxurybathroom	2
luxuryhomes	2
luxuryhouses	2
luxurymirror	1
luxxu	1
maisonvalentina	2
marble	2
marblebathroom	1
moderndesign	2
residential	2
resortdesign	2
sidetable	1
signaturedesign	2
timelesselegance	2
timeout	2
unitedarabemirates	1
vanitycabinet	1
wooden	1

You can try this formula:

=SORT(ARRAYFORMULA({UNIQUE(TRIM(FLATTEN(SPLIT(TEXTJOIN(" ", 1, E2:E254), " ")))),
  COUNTIF(FLATTEN(SPLIT(TEXTJOIN(" ", 1, E2:E254), " ")), UNIQUE(TRIM(FLATTEN(SPLIT(TEXTJOIN(" ", 1, E2:E254), " ")))))}
), 2, FALSE)

Sample Output:

#	3
#maisonvalentina	2
#luxuryhouses	2
#luxurybathroom	2
#bathtub	2
#inspiration	2
#dailyinspo	2
#timelesselegance	2
#dreambath	2
#signaturedesign	2
#moderndesign	2
#timeout	2
#residential	2
#resortdesign	2
#marble	2
#homesociety	2
#homesocietystudio	2
#homesocietycollection	2
#homeinspirationideas	2
#covetgroup	2
#insplosion	2
#luxuryhomes	2
#dreambathroom	1
#vanitycabinet	1
#luxurymirror	1
#decorinspo	1
#london	1
#londonliving	1
#archdaily	1
#wooden	1
#luxxu	1
#sidetable	1
#unitedarabemirates	1
#abudhabidesign	1
#elledecor	1
#marblebathroom	1
#brabbu	1

References:

ArrayFormula , Sort , Flatten , Unique , CountIf , Split , TextJoin

本文标签： functionCount Unique HashtagsStack Overflow