[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Google Docs Spreadsheet - Counting Occurances

Say I have a column "colleges" in the Google Docs Spreadsheet as shown below.  I want to know the number of times  each  item appears.  Stanford 4, Harvard 2, Yale 2, Brown 1.  What formula or feature would I use to get this distribution?

Stanford
Stanford
Yale
Princeton
Stanford
Harvard
Harvard
Yale
Brown
0
amigan_99
Asked:
amigan_99
1 Solution
 
Brad RubinCommented:
OPTION 1
Put your values above in column A.

In column B, label each college once.
Sample Column B:
Stanford
Yale
Princeton
Harvard
Brown

In column C, use a COUNTIF formula where column A is the range, and the name of the college in column B is the criterion.

=COUNTIF(A$2:A$10,B2)

Use the dollar sign on the numbers of the range so you can drag the formula down and copy against all the values in Column A.

Column C will produce counts for each college....

OPTION 2:
Alternatively, you can just create a column B for the values where the formula equals =COUNTIF(A$2:A$10,A2)...you will get a count every time you repeat a college, but it will still work.
0
 
amigan_99Author Commented:
Those are great.  Thank you.  I also found these helpful..

In col J:

=UNIQUE(D:D)  

and then in column K:

=COUNTIF(D:D, J1)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now