count cells with time and date

[count celld with date and time]

Hi, could somebody please help!

cell contain date and time
e.g. " 22/08/2009 14:34"

loads of them in the same column lets say column "A"

I have 5000+ cells per day transfered from another system,

I need to know everyday, how many of these 5000 cell iin column A is from
22/08/2009, 23/08/2009, 24/08/2009....etc (can ignore the time)

I have tried using formula
=SUM(COUNTIF(A:A,{"22/08/2009"}))

but it does not work, as the time within the cell is causing non exact
match, is there a way to go round this, and count how many cells is from each
date?
I have formated the cell to show dates only but the cell still contain time,
and still does not work!

all your help is greatly appreciated, thx a million

 

[Mike H]

Hi

=COUNTIF(A:A,"22/8/2009")

or use a cell ref where b1 contains the date

=COUNTIF(A:A,B1)

Mike

 

[Gary''s Student]

Say A1 thru A30 contains:

19/08/2009 00:16
19/08/2009 00:00
15/08/2009 14:33
14/08/2009 01:39
14/08/2009 19:49
18/08/2009 06:06
22/08/2009 12:33
22/08/2009 08:06
20/08/2009 23:07
17/08/2009 09:05
15/08/2009 14:56
23/08/2009 08:56
23/08/2009 08:27
14/08/2009 10:38
19/08/2009 22:57
14/08/2009 13:11
21/08/2009 14:51
18/08/2009 05:52
15/08/2009 07:29
20/08/2009 22:58
17/08/2009 02:26
18/08/2009 18:14
14/08/2009 10:25
21/08/2009 00:54
18/08/2009 04:23
15/08/2009 09:54
20/08/2009 19:18
18/08/2009 22:07
16/08/2009 08:09
15/08/2009 18:48

and we want to count the number of 22/8/2009's

If we displayed the values as numbers, we would see:

40044.0115
40044.0006
40040.6067
40039.0693
40039.8260
40043.2548
40047.5234
40047.3380
40045.9635
40042.3791
40040.6228
40048.3724
40048.3524
40039.4432
40044.9563
40039.5495
40046.6194
40043.2447
40040.3118
40045.9573
40042.1019
40043.7598
40039.4345
40046.0376
40043.1828
40040.4126
40045.8046
40043.9217
40041.3396
40040.7839

So we really need to count the number of 40047's

Try:

=SUMPRODUCT(--(INT(A1:A30)=40047))

 

[Dave Peterson]

One more:

=sumproduct(--(text(a1:a100,"mmddyyyy")="08222009"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===============
I think I'd consider using a pivottable, too.

You could group by date (and ignore the time).