Repeat Dates and Roles using Column Dates from (E to I)-CodePudding

I have some Data where, there are 8 columns. I want to keep Column, B,E,F,G,H and I.

In Column B I have Some positions and in other columns E,F,G,H and I i have dates. I want to convert the dates as 1 to count the Roles, to know that how many times the date is repeated in "Headers" for the "Roles".

I would really appreciate the help if it can be achieved using formula.

DATA

RESULT

Google Sheets Link

CodePudding user response：

try:

=INDEX(QUERY(QUERY(SPLIT(FLATTEN(IF(ISDATE_STRICT(E4:I19), 
 E4:I19&"×"&B4:B19&"×"&SEQUENCE(1, 5)&"×"&ROW(B4:B19), )), "×"), 
 "select Col1,Col2,count(Col2) 
  where Col2 is not null 
  group by Col1,Col2,Col4 
  pivot Col3"), 
 "offset 1", 0))