yogi_Count The Number Of Occurrences Of An Entity And Present The Results As Specified

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #576    Jun 03, 2012     www.energyefficientbuild.com.


user studenikin.Ivan said:
Report based on a single column
Hi everyone.

I have the following spreadsheet:

Item # |   Cars

--------- --------------

  1        BMW

  2        Porsche

  3        Mercedes

  4        BMW

  5        Porsche

  6        BMW

How do I build a report where X axis is name of Car, and Y axis is number of times that corresponding car appears in the Cars column.

So in the example above desired Data Table (which the report is based on) should look as follows:

Car Name | Occurrence

----------  ----------

Mercedes      1

Porsche       2

BMW           3

------------------------------------------------------------------------------------------------------

I suggested to studenikin.Ivan to use the following QUERY formula:

=query(A:B,"select B,count(A) where A is not null group by B") 

----

studenikin.Ivan responded:

Hi yogia,

Thanks! You solution works perfect for me except one thing. First row of the table looks weird:

Car         count Item#

                       count

BMW        3

Mercedes        1

Porsche        2

----

Well I used the label option in the QUERY function for the headers, and I ordered the result in descending order by occurrences -- so the report is almost identical to what the OP wanted