Countif with reference to multiple columns having same name

[pattyhsu]

I'm looking to count the number of Marisol's based on the date
For example:
on 8/23: there are 9 Marisol, 5 Cindy, etc.

However, because 8/23 spams across 3 columns, I'm not sure how to structure the formula

Please help, thank you!

 

[Peter Bartholomew]

If you have XLOOKUP you could look up the first instance of a date and return the entire 'Front' column from the rota. This is repeated looking for the final (unnamed) column and the colon ':' operator combines the two to give the day's rota as a single range.

= COUNTIFS(
     XLOOKUP(singleDate, date, rota,,, 1):
     XLOOKUP(singleDate, date, rota,,,-1),
  empName)

This allows you to nest the range within a COUNTIFS function to count the occurrences of the given employee on the chosen day.

 

[pattyhsu]

If you have XLOOKUP you could look up the first instance of a date and return the entire 'Front' column from the rota. This is repeated looking for the final (unnamed) column and the colon ':' operator combines the two to give the day's rota as a single range.

= COUNTIFS(
     XLOOKUP(singleDate, date, rota,,, 1):
     XLOOKUP(singleDate, date, rota,,,-1),
  empName)

This allows you to nest the range within a COUNTIFS function to count the occurrences of the given employee on the chosen day.

Thank you! Is there any way doing it in Google Sheets by any chance? I noticed that XLOOKUP is not available in Google Sheets : /