CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup
Chris Barrett
Chris Barrett

Reputation: 601

Turning formula into ArrayFormula

I have this formula for counting matches in Contents!$B$2 in J4. 

=ARRAYFORMULA(IF(NOT(REGEXMATCH(J4, 
"\w")),0,SUM(COUNTIF(REGEXREPLACE(SPLIT(J4,","),"
[\s]",""),REGEXREPLACE(SPLIT(Contents!$B$2,","),"[\s]","")))))

The formula is in K4 but ideally, I want it to work right down J returning values to the relevant adjacent K. I tried this but it didn't work. Just filled the cell the formula was in

=ARRAYFORMULA(IF(NOT(REGEXMATCH(J4:J, 
"\w")),0,SUM(COUNTIF(REGEXREPLACE(SPLIT(J4:J,","),"
[\s]",""),REGEXREPLACE(SPLIT(Contents!$B$2,","),"[\s]","")))))

I know I can just fill down but the issue is users inserting rows.

Thanks in advance for any assistance

Upvotes: 1

Views: 44

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(IF(LEN(A2:A), IFERROR(VLOOKUP(A2:A, 
 QUERY(TRIM(TRANSPOSE(SPLIT(Contents!B1, ","))), 
 "select Col1,count(Col1) group by Col1", 0), 2, 0), 0), ))

0

Upvotes: 1

Related Questions