0

I have a sheet in which I have 2441 words (hashtags) on their own rows in the first column and in the next (adjacent) column, a number related to them (occurrences of the hashtag in our data).

I want to sum together the occurrences of hashtags, which mention any word from a certain list. The word can be either as part of the hashtag or the whole hashtag for the number in the adjacent column to be counted.

I searched the forum and found this helpful thread I tried to incorporate, but unfortunately I am still doing something wrong.

My current formula is:

=ArrayFormula(sum(sumif(A2:A20,{"*business*"; "*entrepeneur*";"*shop*";"*biz*";"*marketing*";"*hustle*"},B2:B20)))

As you can see from the screenshot, the formula doesn't sum all of the instances from the given range. Have I formatted something in the formula wrong? Or well, I obviously have, but I wonder which part it is.

Sheet screenshot

Sample data - text format

Hashtag Count
internationalwomensday 1148
womensupportingwomen 81
womeninbusiness 77
girlpower 72
womenempowerment 69
bossbabe 50
women 48
love 47
womensday 47
smallbusiness 40
entrepreneur 39
strongwomen 36
fashion 34
iwd2021 33
motivation 33
femaleentreprencur 26
beauty 25
bosslady 24
Improve this question
3
  • Welcome to Web Applications. There is a typo. The formula has entrepeneur (missing r between p and e ) but the values in B2:B20 have entrepreneur
    – Rubén - Volunteer Moderator - ♦
    Commented Mar 29, 2021 at 16:41
  • Actually, in addition to Rubén's note, you also have "entrepreneur" spelled wrong in a hashtag (Row 17). The count will always be off if things are spelled wrong from the start.
    – Erik Tyler
    Commented Mar 30, 2021 at 3:44
  • Thank you both! Embarrassed about the typos, glad you pointed them out (working on this late as a non-English-native is a poor excuse). The table "sample data - text format" was generated automatically from my screenshot, so the Row 17 typo is not actually in the data, although some typos probably exist on the later rows of the file (after all, people do write hashtags wrong sometimes).
    – Yolko
    Commented Mar 30, 2021 at 7:28

1 Answer 1

Reset to default
1

Please note the comments made directly to your original post, noting that you have spelling errors in both the formula and the Col-A data.

However, assuming that your spelling were correct in Column A, you could use this in F1:

=SUM(FILTER(B2:B,NOT(ISERROR(REGEXEXTRACT(A2:A,"business|entrepreneur|shop|biz|marketing|hustle")))))

Here, SUM acts only on the FILTERed subset where REGEXEXTRACT was able to find any of the patters found between the 'or' pipes (e.g., "x|y|z|...").

If you think there may be spelling errors, you can reduce some words (like "entrepreneur") to a unique portion of the word that you think will most likely be spelled correctly in all of (or the majority of) the raw data: perhaps "neur" or "entre". But this method would still require that you spell that portion correctly in the formula.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.