I have a Google Sheet named "Calendar" that is formatted like this:
07/12/2024 | 07/11/2024 | 07/10/2024 | 07/09/2024 | 07/08/2024 | 07/05/2024 | 07/04/2024 | 07/03/2024 | 07/02/2024 | 07/01/2024 | 06/28/2024 | 06/27/2024 | 06/26/2024 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
George Washington | Out - Called Off - Sick | Here | Here | Out - Called Off - Car trouble | Here | Here | Out - No Call/No Show | Here | Here | Here | Here | Here | Here |
Ben Franklin | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here |
Thomas Jefferson | Here | Here | Here | Here | Here | Here | Here | Here | Out - Scheduled Off - Vacation | Out - Scheduled Off - Vacation | Out - Scheduled Off - Vacation | Out - Scheduled Off - Vacation | Here |
John Adams | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here |
Patrick Henry | Here | Here | Here | Here | Here | Out - Scheduled Off - Doctor appointment | Here | Here | Here | Here | Here | Here | Here |
Teddy Roosevelt | Out - Scheduled Off - New baby | Out - Called Off - New baby | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here | Here |
Names of employees are in Calendar!A2:A like so:
On a separate tab named "Attendance", in cell A2:A, I have all the names in Calendar!A2:A. I am trying to figure out how to count how many times the value "Out" appears in the row associated with each employee. I have tried many different formulas, but I can't get any results.
Here is how it should look on "Attendance" sheet, based on the data in "Calendar" sheet:
With the following formula, I can get the actual results, but not the count: =INDEX(Calendar!$B$2:$N$7, MATCH(A2,Calendar!$A$2:$A$7,0))
. Also, I would prefer to achieve this with an ARRAYFORMULA
.
You may try:
=map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,countif(filter(Calendar!B:Z,Calendar!A:A=Σ),"*Out*")))