function - Count Unique Hashtags

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

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

Share Improve this question edited Mar 13 at 2:02 Harun24hr 37.3k6 gold badges23 silver badges45 bronze badges asked Mar 13 at 0:09 Social Maison Valentina 11 silver badge

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

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

function - Count Unique Hashtags - Stack Overflow

2 Answers 2

与本文相关的文章

评论列表(0)