CODEWITHSUNDEEP
google-sheetscount
santos1998
santos1998

Reputation: 11

Count Values under Columns with Same Name Google Sheets

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 link to Google Sheet with actual data.

I have tried countif and countifs in Google Sheets, but can't figure out how to get the count right based on column name.

Upvotes: 1

Views: 1347

Answers (3)

The God of Biscuits
The God of Biscuits

Reputation: 3177

The following is a more compact approach than the previous answers:

=arrayformula(query(split(flatten(A1:AE1&"|"&A2:AE18),"|"),"select Col2,count(Col2) group by Col2 pivot Col1"))

N.B. I'm assuming the order of the grouped values in each column is irrelevant, so the QUERY default of lexicographical ordering is fine.

Upvotes: 0

Ping
Ping

Reputation: 911

sample

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.

  1. apply UNIQUE() and FLATTEN() to A1:AE1, to get the unique entries of column names.

  2. apply UNIQUE() and FLATTEN() to A2:AE18, to get the unique entries of data.

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

  4. create Arrays with {}, which...

    • 1st column's value is a blank, followed by TRANSPOSE(UNUM) in the row,

    • 1st row's value is a blank, followed by ULET in the column.

    • inside the above said range, use MAKEARRAY() to create results.

  5. 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.

  6. 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.

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

Upvotes: 1

Nabnub
Nabnub

Reputation: 1055

You can try this:

= ARRAYFORMULA(
    query(
        query(
            SPLIT(TRANSPOSE(SPLIT(
                    QUERY(
                        TRANSPOSE(
                            QUERY(
                                TRANSPOSE(
                                    IF(Original!A2:AE18<>"",
                                        "😊"&Original!A1:AE1&"♥"&Original!A2:AE18, )
                                ),,999^99)
                        ),,999^99),
                    "😊")),
                "♥"),
            "Select Col1,Col2,count(Col2) group by Col1,Col2"),
        "Select max(Col2),Col3 group by Col2,Col3 pivot Col1")
)

Note: (Got inspired by player0 useful answers)

Output:

enter image description here

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

Upvotes: 0

Related Questions