Trying to use sumifs with arrayformula to count the number occurrences of a text-CodePudding

I'm trying to count how many times the text appeared in a range and it is possible that the text can appear multiple times in a single cell.

This is the formula that I'm using and it gives me the result that I need.

=ArrayFormula(SUM(LEN(DATA!M2:M10)-LEN(SUBSTITUTE(DATA!M2:M10,"HELLO","")))/LEN("HELLO"))

But I need to add some filters and I try to do it like the formula below but I'm just getting #N/A Error: Argument must be a range.

=ArrayFormula(SUMIFS(LEN(DATA!M2:M)-LEN(SUBSTITUTE(DATA!M2:M,"HELLO","")), DATA!E2:E, "TEST" , DATA!C2:C, ">=" & D2, DATA!C2:C, "<=" & D3)/LEN("HELLO"))

CodePudding user response：

SUMIFS is not capable for this. try:

=INDEX(SUM(LEN(REGEXREPLACE(SUBSTITUTE(IF(
 (Data!E2:E="TEST")*(Data!C2:C*1>=D2)*(Data!C2:C*1<=D3), Data!A2:A, ), 
 "HELLO", "♦"), "[^♦]", ))))