Count Values under Columns with Same Name Google Sheets-CodePudding

I have a Google sheet which has columns with the same name and there are different values under each column. I want to count the same value that appear under the same column name.

1	2	3	1	2
R	B	C	R	D
D	C	R	B	D

For example, I would like to get the number "R" that appear under column "1", so I would expect a count of 2 for "R" appearing under columns 1.

Here is a

We can read from the table that: R is appearing 40 times under the column named '1', 24 times under the colum named '2', etc...

CodePudding user response：

Try this formula, it outputs an array which shows how many of each letters are contained in each column name:

=LAMBDA(NUMBERS,LETTERS,
 LAMBDA(UNUM,ULET,
  {
   {"",TRANSPOSE(UNUM)};
   {ULET,
    MAKEARRAY(COUNTA(ULET),COUNTA(UNUM),LAMBDA(ROW,COL,
     COUNTIF(FILTER(LETTERS,NUMBERS=INDEX(UNUM,COL)),INDEX(ULET,ROW))
    ))
   }
  }
 )(UNIQUE(FLATTEN(NUMBERS)),UNIQUE(FLATTEN(LETTERS)))
)($A$1:$AE$1,$A$2:$AE$18)

Assume that your sample datarange is A1:AE18.

apply UNIQUE() and FLATTEN() to A1:AE1, to get the unique entries of column names.
apply UNIQUE() and FLATTEN() to A2:AE18, to get the unique entries of data.
use LAMBDA() to name the dataranges and output of step 1 & 2 as:
- NUMBERS (=A1:AE1),
- LETTERS (=A2:AE18),
- UNUM (=UNIQUE(FLATTEN(NUMBERS))),
- ULET (=UNIQUE(FLATTEN(LETTERS))).
create Arrays with {}, which...
- 1st row is a blank, followed by TRANSPOSE(UNUM),
- 1st column is a blank, followed by ULET.
- inside the above said range, use MAKEARRAY() to create results.
MAKEARRAY() set an array by defining the length of ROW and COL, which we uses...
- COUNTA(ULET) as the number of rows and,
- COUNTA(UNUM) as the number of columns.
inside MAKEARRAY(), you also need a LAMBDA() to apply what to do with each CELL of the new created array, each CELL is accessed by the ROW and COL index.
in our case, we set up the row and col number of the new array using ULET and UNUM. Therefor, the index of each CELL of the new array will be equal to the index of each value inside ULET and UNUM, we can than take that as reference and use COUNTIF() with FILTER() to calculate the number of repeats of each letter in each column name.