Count Days if they exist - Excel XP

[TAJ Simmons]

Hi,

I'm trying to find a way to count the number of entries per day.

e.g. Data

Cells:
A1 = 01-Jan-04
A2 = 01-Jan-04
A3 = 01-Jan-04
A4 = 02-Jan-04
A5 = 03-Jan-04
A6 = 03-Jan-04

Results

1st Day of the month = 3
2nd Day of the month = 1
3rd Day of the month = 2

Ultimately I'm trying to do a graph of the days of the month with the results as the bars. So I just need the results in
another column of data.

Excel 2002/XP

Cheers
TAJ Simmons
microsoft powerpoint mvp

awesome - powerpoint backgrounds,
free powerpoint templates, tutorials, hints and tips etc
http://www.powerpointbackgrounds.com

 

[Peo Sjoblom]

=SUMPRODUCT(--(DAY(A1:A6)=1))

you could use countif as well but

you can copy down using an a help column as follows

=SUMPRODUCT(--(DAY($A$2:$A$7)=ROW()-ROW($C$2)+1))

the row part is just to make sure the result stay the same if you insert a
row at the top whereas

=SUMPRODUCT(--(DAY($A$2:$A$7)=ROW(1:1))

if you inserted a row above the formula the result would be skewed, you
could also use this formula

=COUNTIF($A$1:$A$6,"="&DATE(2004,1,ROW()-ROW($C$1)+1))

the C1 means that the formula starts in row 1, if row 2 use a reference to
row 2 ($C$2)

--

Regards,

Peo Sjoblom

Hi,

I'm trying to find a way to count the number of entries per day.

e.g. Data

Cells:
A1 = 01-Jan-04
A2 = 01-Jan-04
A3 = 01-Jan-04
A4 = 02-Jan-04
A5 = 03-Jan-04
A6 = 03-Jan-04

Results

1st Day of the month = 3
2nd Day of the month = 1
3rd Day of the month = 2

Ultimately I'm trying to do a graph of the days of the month with the

results as the bars. So I just need the results in