Google Spreadsheet Query count based on column header

Question

I have a spreadsheet that looks like this:

Name 8/13/2020 | 8/17/2020 | 8/20/2020
John    OT          OT          OT
Bob     OT          AL          OT
Echo    A           LE          OT

I would like to enter in one cell a date and then have it output me how many people have "OT", "AL", or "LE" in their column. As such I thought about using the query to do this:

=QUERY(MySheet!B:P, "SELECT COUNT(D) WHERE D = 'OT' OR D = 'AL' OR D = 'LE' LABEL COUNT(D) ''")

However, I always have to specify the column on which it has to count, is there a way I can not specify count and instead have it match up the correct column based on a date I enter in another field?

If you know of a different way I can do this that does not uses query, that is fine too as an answer.

Answer 1

try:

=COUNTIF(INDIRECT(
 ADDRESS(6,         MATCH(B2, 5:5, 0))&":"&
 ADDRESS(ROWS(A:A), MATCH(B2, 5:5, 0))), "OT")

---

or:

=ARRAYFORMULA({A6:A, INDIRECT(
 ADDRESS(6,         MATCH(B2, 5:5, 0))&":"&
 ADDRESS(ROWS(A:A), MATCH(B2, 5:5, 0)))})

---

or:

=ARRAYFORMULA(QUERY({A6:A, INDIRECT(
 ADDRESS(6,         MATCH(B2, 5:5, 0))&":"&
 ADDRESS(ROWS(A:A), MATCH(B2, 5:5, 0)))}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1
  pivot Col2"))