CODEWITHSUNDEEP
exceldate
LNA
LNA

Reputation: 1447

Can I use the CountIf function to count the # of cells containing certain date keywords?

I have a set of data that looks something like this:

11/8/12 5:20

11/7/12 15:57

11/7/12 13:51

11/7/12 10:47

11/7/12 8:00

Just in Excel, I want to be able to count the number of rows with 11/8/12, and the number of rows beginning in 11/7, etc. I have a pretty long list of dates and times, but I'm only interested in the dates.

I tried =COUNTIF(A1:A235, "<11/8/12 5:20") to get everything before 11/8, but obviously this doesn't work because I need to type in the time. Does anyone know how to:

1) make the timestamp irrelevant? 2) somehow search for rows that "contain" a certain date value and count up the rows that contain that value?

Upvotes: 1

Views: 20110

Answers (2)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

You can insert in a cell e.g. F7 the date which should be used and then your formula will look like this

=COUNTIF(E8:E12;"<"&F7)

And then in case that your F7 will contain 11/7/12 14:00 the countif will return 3

NOTE: expecting the british date 11/7/12 is dd/mm/yy

EDIT: Date function

When I've seen another answer, I would like to show another approach, which is culture safe. Whenever we will put some strings representing dates, it could happen that a collague has different regional settings (e.g. British dd/mm/yy vs US mm/dd/yy)

That's why I placed the date into separated cell, representing the Date regardless of the culture. If that all have to be placed inside one cell, the correct approach is to use Date function

=COUNTIF(E8:E12;"<"&DATE(2012;7;11)+TIME(14;0;0))

Upvotes: 3

barry houdini
barry houdini

Reputation: 46341

In Excel dates are integers and the time is a fractional value so you can simply count between one date and the next (date +1) to get everything on a single date, e.g. for everything on 11/7

=COUNTIF(A1:A235, ">=11/7/12")-COUNTIF(A1:A235, ">="&"11/7/12"+1)

or in Excel 2007 or later you can use COUNTIFS

=COUNTIFS(A1:A235, ">=11/7/12",A1:A235,"<"&"11/7/12"+1)

for either one you can use a cell reference with the date as Radim suggests, i.e. with date in B1

=COUNTIFS(A$1:A$235,">="&B1,A$1:A$235,"<"&B1+1)

With that last version you can simply list all your dates in B1 down, and then use the formula in C1 copied down to get counts for each date

Upvotes: 2

Related Questions