Excel Dragging countif formula with changing criteria


Joined
Oct 4, 2017
Messages
1
Reaction score
0
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.
 
Ad

Advertisements

Ad

Advertisements

Joined
Feb 21, 2018
Messages
130
Reaction score
48
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.
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top