Counting over aggregated columns in Google Sheets-CodePudding

I have the yellow table shown below, and I'm trying to get the blue table, which aggregates columns B:F by value, and then counts the number of 'x' symbols for each row value of column A.

Is there some basic SQL/array magic formula to get this, please? There must be.

CodePudding user response：

Use this new functions formula

=BYROW(B2:4, LAMBDA(v, COUNTIF(v, "=x")))

Used:

For fun

Update 02

=ArrayFormula(TRANSPOSE(QUERY({
              QUERY(TRANSPOSE(IF(A1:4<>"x",A1:4,1)), 
                               " Select * Where Col1 is not null ", 1)}, 
                               " Select (Col1),sum(Col2),sum(Col3),sum(Col4) Group by Col1 ", 1)))