counting

[afdmello]

I have dates in a column

for eg 30-aug-09 ten times
1-sep-09 5 times
3-sep-09 6 times

I want to add or count all dates of 30-aug-09 as 1
all 1-sep-09 as 2 and so on...
my goal being I want to count number of dates

pls help

afd

 

[FSt1]

hi
provided that the dates are dates with no times attached, you could use the
countif function.
with a date you want to count in A1 and all dates to count in column
A.........
=countif(A1:A25, A1)
or you could put your count date in a cell off to the side and change the
date in that cell to change the formula results.

Regards
FSt1

 

[afdmello]

Thanx FSt1 for the prompt response.

I tried that but that is not what I want as you see this is a table with
outstanding survey points checked some workdays in a construction project.
I want the number of surveys done to date. in one CELL.

I have 10 survey points for August 30, 5 points for sept 1, 6points for
sep3, 4 points on sep 4 so on...

Now I want to count the number of surveys done. and use the dates column for
doing it.
from the above data you can say ( aug30( 1), sept1(2), sep3(3), sep 4(4)) so
4 surveys done.

can this be achieved by any formula??

Afd

 

[FSt1]

hi
now i am a tad confused.
do you mean that you have 10 seperate entries for Aug-30 or the number 10
beside aug-30. if the first then that should be just a count formula
=count(A2:A100)
if the last, that sounds like a simple sum formula.
how is your data layed out?

Regards
FSt1

 

[afdmello]

have a column C with 10 aug 30 2009 entries For eg(C8:C17), 5 entries of
Sep 12009 (C18:C22), 6 entries for september 3 2009(C23:C28).

I want excel to calculate(count) in cell D1

Any date entry with august 30 2009 as 1
any date entry with september 1 2009 as 1
any date entry with september 3 2009 as 1
and thus make a total count
as this will give me a count of the number of survey days

hope I am able to make it clear

afd

 

[T. Valko]

It sounds like what you want is a count of the unique dates.

Assuming your dates are true Excel dates...

=SUM(--(FREQUENCY(C8:C100,C8:C100)>0))

 

[FSt1]

hi
still not sure but if i do understand try this...
=COUNT(IF(FREQUENCY(A1:A25, A1:A25)>0,1))

adjust ranges to suit.
Regars
FSt1

 

[Ashish Mathur]

Hi,

You can also try this. This formula assumes that there are no blanks in the
range

=sumproduct(1/countif(range,range))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[afdmello]

Thank you very much FSt1. I counted the dates and it was tallying with the
result of your formula.
why does the frequency have the same range.
I tried to learn the significance of frequency why does the cell with ONLY
the frequency formuala always return a result of 10

Thanks once again

Afd