Counting over aggregated columns in Google Sheets

Fr. 0
Viewed 32

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.

enter image description here

google-sheets
1 Answers

Use this new functions formula

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

enter image description here

Used: BYROW, LAMBDA, COUNTIF
v is the array_or_range

Update

={ A2:A4, BYROW(B2:4, LAMBDA(vv, COUNTIF(vv, "=x")))}

enter image description here

For fun

enter image description here

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)))

enter image description here

Osm0
Related