# Counting Dates

#### Sing01

##### New Member
I need to count the number of dates that fall on or before a given date ie...the given date is 12jan05 and I have the following dates:

11dec04
10nov04
15feb05
21mar05

In this example the answer should be: 2 If the data date were 15feb05, the answer would be 3

I tried using countif(range;"<=data date") where data date is the cell address of the data date (12jan05). I need a simple solution...an array might be a problem

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Tyep the date you want to chack in (say) cell B1.

Then:
=countif(range,"<=B1")

This assumes that the data is formatted values.

This is the formula I have been using, but for some reason it keeps returning 0 instead of expected answer (3). You are probably right...has something to do with date format

Can you explain what type of date format I should be using....

Really need to get this to work

Thanks

This will work. Any format will do:
=SUMPRODUCT(--(\$A\$1:\$A\$4<=B1))

Works like a charm.....Thanks

tactps said:
This will work. Any format will do:
=SUMPRODUCT(--(\$A\$1:\$A\$4<=B1))

No, any format won't do. That formula requires true dates.

BTW, you wrote:

=countif(range,"<=B1")

while it should be:

=COUNTIF(Range,"<="&B1)

Replies
1
Views
265
Replies
2
Views
238
Replies
3
Views
433
Replies
14
Views
875
Replies
4
Views
388

1,203,531
Messages
6,055,939
Members
444,838
Latest member
Eng76

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back