1

I am analyzing data having 450000 cells in a column range which is taking a long time to find occurrences of a specific word hence hanging Excel. The formula I am using is =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,word,"")))/LEN(word)

Please help me on this query.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Taib Bilal
  • 19
  • 1
  • It's an array formula on a lot of data so do expect it to take a long time. What did you use a the range-reference? Also, would the word always only occur just once in a cell? – JvdV Jan 07 '22 at 13:38
  • [`COUNTIF`](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)? – derpirscher Jan 07 '22 at 13:42
  • @ashleedawg No, you can also use wildcards (`?` and `*`) with countif. But if the respective word appears more than once in the cell, it will be only counted as 1 ... – derpirscher Jan 07 '22 at 13:45
  • @ashleedawg Yes, it counts *cells* meeting a certain criterion. But as the question is quite vague, nobody knows, whether that will be enough or not ... – derpirscher Jan 07 '22 at 13:52
  • I'm not sure if this would be any faster (rather than using `SUMPRODUCT`, which is [known](https://www.google.com/search?q=excel+sumproduct+slow) for it's poor performance): `=SUM((LEN(A:A)-LEN(SUBSTITUTE(A:A,word,"")))/LEN(word))` to count occurrences of `word` in range `A:A`. (This is an array formula so hold CTRL while hitting Enter.) – ashleedawg Jan 07 '22 at 13:52
  • @ashleedawg I don't see how that could possibly be faster. You're referencing all 1048576 cells in column A. I assume `range` is set to be more efficient than that. – Jos Woolley Jan 07 '22 at 13:54
  • @JosWoolley - as an [array formula](https://support.microsoft.com/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7), it will only check populated cells. I haven't tested performance but I bet it's faster than using `SUMPRODUCT`. Further performance tips [here](https://learn.microsoft.com/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions) (including some specific to array formulas.) ...([tag:VBA] would probably fastest by far, but the question isn't tagged that way) – ashleedawg Jan 07 '22 at 14:00
  • @ashleedawg I have no idea where you heard that, but it's completely incorrect. – Jos Woolley Jan 07 '22 at 14:01
  • @JosWoolley - try it. – ashleedawg Jan 07 '22 at 14:02
  • Close all workbooks, open a new workbook and paste your formula into a range of, say, 500 cells, e.g. `B1:U25`. Now watch Excel churn away for several minutes. You really should not post such misinformation: array formulas do not "check populated cells" only. – Jos Woolley Jan 07 '22 at 14:03

0 Answers0