[SOLVED] Is there a way to COUNTIF the number of partial name matches (REGEXMATCH) within a column of cells?

Is there a way to COUNTIF the number of partial name matches (REGEXMATCH) within a column of cells?

Im trying to make an Absent & Late counter based off answers from a google form. The google form collects whos absent and late through checkboxes and in the linked sheets all the names are inputted as name1, name 2, name 3...

The problem I currently have is that I can't make a counter if I can't REGEXMATCH a whole column.

I tested it out with just 1 cell through this formula and it worked:

=IF(REGEXMATCH(C2,"SMITH JOHN"), COUNTIF(C2, "SMITH JOHN"))

I just need this to work for the whole column.

This is what I want to happen:

FORM DATA ON COLUMNS B, C, & D

DATE	ABSENT	LATE
09/12/23	SMITH JOHN, CRAIG AMY	HOPE MITCH
09/13/23	HOPE MITCH, SMITH JOHN	CRAIG AMY

Expected Output: COUNTER ON COLUMNS F, G, H

NAME	ABSENT	LATE
SMITH JOHN	2	0
CRAIG AMY	1	1
HOPE MITCH	1	1

Please let me know if theres any way to achieve this!

Solution

Here's one approach using the COUNTIF function:

=map(F2:F,lambda(Σ,if(Σ="",,index({countif(split(C:C,", ",),Σ),countif(split(D:D,", ",),Σ)}))))