Iterate over list in a cell to countif it meets a criteria in another in Google Sheets-CodePudding

I'm wondering if there's a way to iterate over a list of values in a single cell, to then look up the value of each item in that last, and then to count how many times it occurs in another list. I would also be ok if we just put a list of the qualifying IDs in this cell. Here's a link to a demo of the data (

CodePudding user response：

If you would like an array solution (i.e., one formula per column that produces all results for all rows in that column), try this:

1.) For best performance, regardless of approach, you should keep each sheet trimmed to only the max number of columns and rows you anticipate ever needing. So I recommend that you first select and delete Rows 201 to 1001, and delete Column L to X (or delete any rows and columns beyond the maximum number you need in your real sheet). The formula proposed below will work whether you take this step or not; but you'll improve performance speed if you take this step, because every array formula must process all rows its provided, whether blank or not.

2.) Select and delete everything from D3:F (i.e., D3 as the upper left of the range and the last row in F as the bottom right of the range).

3.) Place the following formula into cell D3:

=ArrayFormula(MMULT(REGEXMATCH(IFERROR(LOWER(VLOOKUP(ARRAY_CONSTRAIN(SPLIT($B3:$B&REPT(",0",100),",",1),ROWS($B3:$B),100),$H:$J,3,FALSE))),LOWER(D2))*1,SEQUENCE(100,1,1,0)))

You will immediately see results fill in for all rows in Column D.

4.) Drag this formula to the right, into cells E3 and F3. Results for those columns will fill in accordingly for all rows.

The formula allows for up 100 comma-separated values in each cell of B3:B. However, if you think you won't ever have more than, say, 50, change the three instances of 100 in the formula to 50 (or to just above whatever you think the maximum number of values per cell will ever be). The closer you can get to that max, the more optimal the processing time will be.

In addition...

If you'd like to replace your Col-C values with a single array formula:

1.) Delete everything from C3:C.

2.) Place the following variation of your original Col-C formula in cell C3:

=ArrayFormula(IF(A3:A="",,LEN(B3:B)-LEN(SUBSTITUTE(B3:B,",","")) 1))