how to count values, if they fall under a separate category and within a group

Question

Columns D:H show my initial info, where two workstreams (Column D) have different issues occur (Column F:H). These issues fall under one of three categories, under Columns A:B. I want the chart to show the count of the category each issues falls under, making sure it's also showing that it's within a certain workstream.

what i was working with so far is below to match issues to the three categories

=INDEX(A:B, MATCH(F1, A:A, 0), 2)

and below to count the times a value is within the workstream group (A or B).

=countif(flatten(filter(F:H,D:D="A")),"one")

How can I combine these two?

https://docs.google.com/spreadsheets/d/1hs-Srt-qdOR44V_rmvKF-rC5DThRrQrrCfiA_jAvnH0/edit?usp=sharing

Answer 1

delete J:M range and use in D1:

=INDEX({QUERY(SPLIT(FLATTEN(D1:D9&"×"&VLOOKUP(F1:H9, A1:B12, 2, 0)), "×"), 
 "select Col1,count(Col1) where Col2 is not null 
 group by Col1 pivot Col2 label Col1'workstream'"); "TOTAL", 
 TRANSPOSE(MMULT(1*QUERY(
 QUERY(SPLIT(FLATTEN(D1:D9&"×"&VLOOKUP(F1:H9, A1:B12, 2, 0)), "×"), 
 "select count(Col2) where Col2 is not null 
  group by Col2 pivot Col1"), "offset 1", ), 
 SEQUENCE(COUNTUNIQUE(D1:D9), 1, 1, 0)))})

formula explanation