Formula help please

BCowans 

I think SergeiBaklan 's code is version neutral.  I am the one who uses problems to explore what could be done that is specific to 365.  When I first embarked on this voyage of discovery, my solutions were pretty useless to the OP but now there is an increasing proportion of users who could use the solutions, even though they may fail to meet expectations.

I had another look at the problem you specified today and finished up with

= ArrayCountIfλ(tableBody, "Y", tableHeader, header, tableAttribute, color)

where

ArrayCountIfλ
= LAMBDA(tbleBody, value, tbleHdr, hdr, tbleColor, clr,
    LET(
      mask,   ISTEXT(hdr) * ISTEXT(clr),
      hdrArr, IF(mask, hdr),
      clrArr, IF(mask, clr),
      MAP(hdrArr, clrArr, LAMBDA(h, c, COUNTIFS(XLOOKUP(h, tbleHdr, tbleBody), "Y", tbleColor, c)))
    )
  )

One day and I might even get to use it myself!

BCowans 

Something of an effort to bypass Microsoft's array of array specification error by using MAP over a 2D array.

= LAMBDA(hdr,clr,table,
    LET(
      tbleHdr,   TAKE(table, 1,-2),
      tbleBody,  DROP(table, 1, 1),
      tbleColor, DROP(table, 1,-2),
      mask,      ISTEXT(hdr)*ISTEXT(clr),
      hdrArr,    IF(mask, hdr),
      clrArr,    IF(mask, clr),
      MAP(hdrArr, clrArr, LAMBDA(h,c, COUNTIFS(XLOOKUP(h, tbleHdr, tbleBody), "Y", tbleColor, c)))
    )
  )(header, color, Table1[#All])

BCowans 

Depends on which Excel version you are, below possibly could work on practically any one. If second range is named answers

here

in C4 it could be

=SUMPRODUCT(
    (INDEX(answers,0,1)=C$2 )*
    (INDEX(answers, 0, MATCH($B4, INDEX(answers,1,0), 0) ) = "Y" ) )
    /
    SUMPRODUCT( 1*(INDEX(answers,0,1)=C$2) )

and drag it to the right and down.