Counting dates - FRUSTRATED!!!!!

[D Boyles]

Using Excel 2003.

I am trying to count the number of times a date (i.e. 1/1/2009 or 1/8/2009)
appears in a range of cells.

I also want to be able to count the number of times a unit/floor appears on
a gvien date (i.e. CTUN appears 3 times on 01/1/2009 and 6 times on 1/8/2009)
the unit is in one column and the date in another column.

I have tried every kind of function - countif, sumif, count, dcount and I
cannot get the calculation to work. I am sure that it has something to do
with the syntax but I just can't get it right.

Thanks

 

[Fred Smith]

Try this:

=countif(A:A,"="&date(2009,1,1))

Or put the date you are looking for in a cell like b1, and use:

=countif(A:A,"="&B1)

Adjust your range to suit.

Regards,
Fred.

 

[Max]

Suspect that your "dates" data may not be real dates. You can try converting
it all at one go to real dates by selecting the "dates" col, then click Data

Text to Columns, click Next > Next. In step 3 of the wizard, check "Date"

under Col data format, then choose the appropriate date format, eg: MDY from
the droplist. Click Finish

Then ... assuming real dates are in A2:A100, unit/floor (eg: CTUN) in B2:B100
These 2 formulas should work ok

=COUNTIF(A2:A100,--"08 Jan 2009")
to count how many times the date: 08 Jan 2009 appears

=SUMPRODUCT((A2:A100= --"08 Jan 2009")*(B2:B100="CTUN"))
to count how many times CTUN appears in col B
where the date in col A is: 08 Jan 2009

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Neil W]

I have been doing a similar thing today and using the VLOOKUP i can match
dates from a ref cell then add a return columns.

 

[D Boyles]

Thank you both Max & Fred,

I was finally able to get the caluculations and syntax correct. Found I had
time noted in my date field which was screwing things up and in the
unit/floor field I had empty fields which were giving me a #N/A error.

Again Thank you so much this is going to make my life so much easier each
month!!!

 

[Fred Smith]

You're welcome. Thanks for the feedback.

Regards,
Fred.

 

[Fred Smith]

Neil, your post would be much more helpful if you included the formula
you're using.

Regards,
Fred.

 

[Max]

Welcome. Kindly mark ALL responses which help you in some way by clicking the
YES buttons in those responses (like the ones below)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik