google-sheetsgoogle-sheets-formula

How to count number of occurrences in a row for a given value

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:

SShot1

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:

SShot2

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.

Solution

  • You may try:

    =map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,countif(filter(Calendar!B:Z,Calendar!A:A=Σ),"*Out*")))

    enter image description here