Is there a way to evaluate multiple columns and then the resulting rows with an array formula in Google Sheets?

Question

I have a Google Sheet of renewal opportunities and the date that each opportunity move into a new renewal stage, which can be found here. The end goal is to see how many opportunities were in each stage during any given "snapshot date".

I created an INDEX MATCH formula that looks at columns of stage change dates and will return the stage based on the snapshot date given. The problem is that I have to have an additional column for each snapshot date that I want to look at.

=IFERROR(INDEX(SUBSTITUTE($A$1:$G$1," (AUTOMATIC)",""),MATCH(I$1,ARRAYFORMULA(IF($A2:$G2="","",TO_DATE(INT($A2:$G2)))),1)),"Untouched")

Is there a way to evaluate the columns with the INDEX MATCH formula and then the resulting rows, all in one array formula? I want the output to look like the table in Columns O:T.

Answer 1

Here is my attempt:

=ARRAYFORMULA(ARRAY_CONSTRAIN(IFERROR(VLOOKUP(D1:H1&C2:C&"*",QUERY(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(IF(Sheet1!A2:G="",,FLOOR(Sheet1!A2:G-2,7)+2&Sheet1!A1:G1&"|"),,9^99)),,9^99),"| ",0,0)),"select Col1,COUNT(Col1) group by Col1"),2,0)),COUNTA(C2:C),COUNTA(D1:1)))

and my sheet:

https://docs.google.com/spreadsheets/d/1SZcAsALTxu2fpNu3s-4fIq-ms5IDyXijQWF1c49mtkU/edit?usp=sharing

I realize I don't have "untouched" figured out yet because i don't see any values in the "untouched" column, so i'm not entirely clear on the logic of that one yet.

How do the other numbers look? I used FLOOR(...7 ) on the dates, so it'll count anything in the week associated with the monday, not just dates that fall ON that monday.

Answer 2

try:

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 IF(I2:M="",,"♠"&TO_TEXT(I1:M1)&"♦"&I2:M&"♦"),,999^99)),,999^99), "♠")), "♦"), 
 "select Col2,count(Col2) 
  where Col2 is not null 
  group by Col2 
  pivot Col1"))

---

UPDATE:

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 IF(A2:G="",,"♠"&TO_TEXT(A2:G)&"♦"&REGEXEXTRACT(A1:G1, "(.+) \(")&"♦"),,999^99)),,999^99), "♠")), "♦"), 
 "select Col2,count(Col2) 
  where Col2 is not null 
  group by Col2 
  pivot Col1"))