3

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
3

This will be the easiest method.

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

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.

0

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

WA109439 example

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.