Counting unique rows in Google sheets with row output plus count

Question

How do you COUNT the number of unique rows in Google Sheets?
I have searched the knowledge base here but no luck.

My table looks like this :

Inst       vCPU    RAM  OS
t2.large    2       8   Windows
t2.large    2       8   Windows
t2.medium   2       4   Windows
t2.small    1       2   RHEL
t2.micro    1       1   Linux
t2.micro    1       1   Linux

I would like an output like this:

Count  Inst       vCPU    RAM   OS
2      t2.large     2       8   Windows
1      t2.medium    2       4   Windows
1       t2.small    1       2   RHEL
2       t2.micro    1       1   Linux

Answer 1

This will be the easiest method.

=QUERY (A1:D7,"Select Count (A),A,avg(B),avg(C), max (D) group by A")

Answer 2

I'm sure there are many ways to accomplish something like this. One suggestion is to use the =UNIQUE function to output the unique rows, and the =COUNTIFS function to count each instance.

Let's say you have your input table in cells A2:D8. Position the cursor in the cell where you want the output table, let's say F2, and enter the formula =UNIQUE(A2:D). This outputs the input data, leaving out duplicates.

Position the cursor to the right of the output table, in cell J3. In the first data row, enter the formula =COUNTIFS(A$3:A; "=" & F3; B$3:B; "=" & G3; C$3:C; "=" & H3; D$3:D; "=" & I3). This counts rows from the input table, which satisfies the criteria given: The A cell value must equal the value of cell F3, the B cell must equal G3 and so on. The result is the number of rows of the input table that equals the (duplicate-filtered) output table.
Drag this formula down, to cover the rest of the rows.

I have set up an example spreadsheet to demonstrate, feel free to copy it.

Answer 3

With a helper column to string together the columns 'conventional' processing is possible with simple formulae:

A2: =I2&"|"&J2&"|"&K2&"|"&L2
B2: =unique(A2:A)
C2: =countif(A2:A,B2)
D2: =split(B2:B,"|")

Copied down to suit as necessary.

UNIQUE
COUNT
SPLIT