Excel Dragging countif formula with changing criteria

[bxareid]

I have column A with dates in it. Each date may have multiple entries and dates are formatted as 8/14/2017 as example. I need a formula to identify how many times each date is entered into the column. For an example 8/14/2017 may be entered 30 times. I currently have the formula, but need to track everyday and am hoping there is something easy I can do so I don't have to rewrite the formula for every day. Current formula is

=COUNTIF('C:\desktop\[Book1.xlsx]Sheet 1'!$A:$A,"8/14/17")

The file names are not accurate for this purpose. The formula works, but I am trying to see if there is a way I can just drag it down and the date roll to the next day such as next line the formula would change to:

=COUNTIF('C:\desktop\[Book1.xlsx]Sheet 1'!$A:$A,"8/15/17")

Any help would be greatly appreciated.

 

[shivya]

Upload your file

 

[shivya]

=COUNTIF($E$4:E4,E4)

Change range according to you.

 

[AmjiBhai]

Before writing the countif formula you need to setup a button , when clicked
Performs the following...

Workbooks("Book1.xlsx").sheets("Sheet 1").range("A:A").copy activeworkbook.Sheets("My Sheet").range("a1")

Range("A1").CurrentRegion.RemoveDuplicates Columns:=1,Header: =xlyes


Now write countif formula in column b cell b2...

COUNTIF('C:\desktop\[Book1.xlsx]Sheet 1'!$A:$A,B2)

Copy it down wards and you have the dates summary.