Here you have a different situation.
Let’s say you need to count a specific word from a range of cells or to check the number of times a value appears in a column.
Take this example.
Below you have a range of four cells and from this range, you need to count the count of occurrence of the word “Monday”. For this, the formula is:
=SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"Monday","")))/LEN("Monday"))
And when you enter it, it returns the count of word “Monday”.
That’s 4.
⚠️ Important
It returns the count of the word (word’s frequency) from the range not the count of the cells which have that word.
Monday is there four times in three cells.
...let me explain how it works
To understand this function, again you need to split it into four parts.
In the first part, LEN function returns an array of the count of characters from the cells.
The second part returns an array of the count of character from the cells by removing the word “Monday”.
In the third part, LEN function returns the length of characters of wor word “Monday”.
After that, subtracts part one from part two and then divide it with part three...
...it returns an array with the count of the word “Monday” from each cell.
In the fourth part, SUMPRODUCT returns the sum of this array and give the count of “Monday” from the range.
با نام و یاد خدا