0

Using Google Sheets, I need a formula that will tell me two things:

1) Number of instances that identical dates occurred exactly twice
2) Number of instances that identical dates occurred exactly three times

The "real world" application for this task is to count and display how many times a business had two jobs on the same day (2 entries with the same date) AND count and display how many times a business had three jobs on the same day (3 entries with the same date).

As per the column below, 1/2/2015, 1/23/2015, 1/24/2015, 1/30/2015 are each entered twice as the business had two jobs on those days. (We will call them "Double Bookings".)

1/16/2015 is a date entered three times as the business had three jobs on that day. (We will call them "Triple Bookings".) 

1/2/2015
1/2/2015
1/3/2015
1/9/2015
1/10/2015
1/16/2015
1/16/2015
1/16/2015
1/17/2015
1/18/2015
1/23/2015
1/23/2015
1/24/2015
1/24/2015
1/30/2015
1/30/2015
1/31/2015

I would like the formula to give me the following two bits of information:

1) Number of instances that identical dates occurred exactly twice
2) Number of instances that identical dates occurred exactly three times

pnuts
  • 58,317
  • 11
  • 87
  • 139
user4528404
  • 1
  • What attempt (if any, right or wrong) have you made so far? –  Feb 04 '15 at 12:52
  • @Jeeped I've been totally stuck. I am a novice spreadsheets user and always find my answers either in the stacks, via google or youtube. I've been at this for 5 hours and can't get anywhere. i've seen fairly similar posts but nothing close enough where I could extrapolate for my own application. Here are a few links that roughly touched on the idea but not quite the right direction: http://stackoverflow.com/questions/6755544/how-to-detect-uniques-and-count-the-number-of-cells-with-duplicate-data-in-googl and http://stackoverflow.com/questions/14380882/count-distinct-values-in-spreadsheet – user4528404 Feb 04 '15 at 13:01

1 Answers1

0

Simplest may be to create a pivot table (your data added for both Rows and Values) with the latter Summarize by: COUNT.

pnuts
  • 58,317
  • 11
  • 87
  • 139