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!
RAPHAELLE ABBY MONTAYRE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Here’s one approach using the COUNTIF function:
=map(F2:F,lambda(Σ,if(Σ="",,index({countif(split(C:C,", ",),Σ),countif(split(D:D,", ",),Σ)}))))
1
You can use the following formula for ABSENT:
=ARRAYFORMULA(COUNTIF(C2:C,"*"&F2:F4&"*"))
And the following formula for LATE:
=ARRAYFORMULA(COUNTIF(D2:D,"*"&F2:F4&"*"))
Or one formula for both:
=ARRAYFORMULA(BYCOL(C2:D,LAMBDA(c,COUNTIF(c,"*"&F2:F4&"*"))))