How to query count of unique features in google sheets?-CodePudding

I have this table:

Name    | Age
Ann     | adult
Ann     | adult
Andrew  | adult
Mike    | adult
Ann     | teenager
John    | teenager
John    | teenager

I want this output:

Age      | count Name (distinct Names)
adult    |  3
teenager |  2

Unfortunately, I can't go further then this formula:

=QUERY(table; "select B, count(A) group by B"; 1)

where the 'table' is the named range with input data. And it gives me this:

Age      | count Name
adult    |  4
teenager |  3

I need something like:

=QUERY(table; "select B, count(unique(A)) group by A"; 1)

which obviously doesn't work.

So, how can I achieve my target output with quering? I know, I can do that with pivot tables with countunique function, but I want to go without pivot tables.

CodePudding user response：

One option could be QUERY UNIQUE:

=QUERY(UNIQUE(A2:B),"SELECT Col2, COUNT(Col1) WHERE Col2 IS NOT NULL GROUP BY Col2")

CodePudding user response：

You can also make use of this formula:

=ARRAYFORMULA(QUERY(UNIQUE({B:B, B:B & A:A, A:A}), "SELECT Col1, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY COUNT(Col1) DESC LABEL COUNT(Col1)'count Name'", 1))

After

Explanation

The formula makes use of the following functions:

ARRAYFORMULA
UNIQUE
QUERY

In order to find the unique values, the UNIQUE is used for the range needed for the query (A:B) such that the sorting and counting is done on this range. The LABEL is used as well in order to set the header name for the resulted column.

Reference