Hi there
I'm aware about how to count certain words in a range of cells, however some words appear in the same cell as it not being counted as part of the overall total.
is there a way in which i can count every time a certain cell is being used?
Hi there
I'm aware about how to count certain words in a range of cells, however some words appear in the same cell as it not being counted as part of the overall total.
is there a way in which i can count every time a certain cell is being used?
Hi tkred,
I think you are looking something like: Replace the "word" in the cell with "", then see the length of the cell before and after. This is how we count how many times a string is in a cells worth of words.
Not completely understanding your question, this might also give you a tool: https://exceljet.net/formula/count-c...-specific-text
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi MarvinP
Basically lets say I want to count how many times the word Summer is included in every cell from A1:A5 (so 5 cells) However a3 has the word Summer in the cell about 3 times meaning the total amount should be (8), however the amount totals to 5. I want to make sure it counts the overall amount the word is use and not the amount of cells has the world included.
As illustrated in the attached file, it could be done using some helper columns (C:F) populated using: =IFERROR(SEARCH($C$1,$A2,IF(COLUMN()=3,1,B2)+6*(COLUMNS($A$1:A$1)-1)),"")
The count is displayed using: =COUNT(C2:F6)
Let us know if you have any questions.
Edit: Update the formula in C2 (over and down to F6) to read: =IFERROR(SEARCH($C$1,$A2,IF(COLUMN()=3,1,B2)+LEN($C$1)*(COLUMNS($A$1:A$1)-1)),"")
Last edited by JeteMc; 04-17-2020 at 07:44 PM. Reason: Updated formula
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks