# Counting the number of times a date appears in a list

1. ## Counting the number of times a date appears in a list

I'm trying to assess how frequently an operating room is, and isn't, in use.

I need to know:
the number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.

I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.

What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases. But there has to be a better way.

2. If you just want a number of each then you can do it like this.

List start date in E1 and end date in E2 (on attached sheet I've calculated these automatically from the smallest and largest dates in the list but you can change these manually if you want)

Now use this formula

=SUM(IF(FREQUENCY(A\$2:A\$300,ROW(INDIRECT(E\$1&":"&E\$2-1)))=D5,1))

Where D5 contains the occurence number

That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

See attached

see attached

3. another way
put a list of all the dates of the year in say col c starting at c1
then in d1 =COUNTIF(\$A\$2:\$A\$500,"="&C1)
and drag down
you can then see at a glance how many times any day was used
then use
=COUNTIF(\$D\$1:\$D\$500,"=0")
=COUNTIF(\$D\$1:\$D\$500,"=1")
=COUNTIF(\$D\$1:\$D\$500,"=2")

4. You can still summarize the data in a pivot table if that works for you. I took the table that you attached that had the date of service in column A, then in column B I put in the number 1 in each cell to correspond with each date. So, when you run a pivot off of both columns you can put the date in the date of service in the row field and then the cases in the data field. You will then have a date and a corresponding number of cases for that specific day.

Hope that helps.

If you just want a number of each then you can do it like this.

List start date in E1 and end date in E2 (on attached sheet I've calculated these automatically from the smallest and largest dates in the list but you can change these manually if you want)

Now use this formula

=SUM(IF(FREQUENCY(A\$2:A\$300,ROW(INDIRECT(E\$1&":"&E\$2-1)))=D5,1))

Where D5 contains the occurence number

That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

See attached

see attached
Thanks for your response. It's an interesting solution. Unfortunately it doesn't appear to work. If you'll notice there are 86 dates in the list. But if you add up the dates in which there were one case per day (56) and dates on which two cases per day (15) it should add up to 86. But it only adds up to 71. So some cases are not being counted.

Also, if you count the number of days between the start date and stop dates there are 227 days. But if you total all the days in the "occurances" column they add up to 228 days.

6. Yes, there 86 dates in the list but not 86 different dates. As my formulas show 15 dates were shown twice,

15*2+56*1 = 86

There are 228 dates between 8th April and 21st November if you count both start date and end date, which you need to do as you have operations on those dates. The correct formula to count dates, including start and end, would be

=F2-F1+1

Otherwise if there was only 1 date on the list, for instance, then F2 would be the same as F1 and F2-F1 would give zero.

Yes, there 86 dates in the list but not 86 different dates. As my formulas show 15 dates were shown twice,

15*2+56*1 = 86

There are 228 dates between 8th April and 21st November if you count both start date and end date, which you need to do as you have operations on those dates. The correct formula to count dates, including start and end, would be

=F2-F1+1

Otherwise if there was only 1 date on the list, for instance, then F2 would be the same as F1 and F2-F1 would give zero.
Sheesh! Why didn't I see that? Thanks very much. Mystery Solved.

One more question. I know I'm supposed to label the thread as solved. The instructions say I need to:
1) Click the EDIT button on your first post
2) Click the GO ADVANCED button
3) Select SOLVED from the Title dropdown (under the Reason for Editing box)
4) Click the SAVE CHANGES button

However, when I do this I can't find the Title dropdown mentioned in step 3.

8. I marked it "SOLVED" for you. I agree it's not particularly easy to follow.....

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1