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
--
D

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.

"D Boyles" wrote in message
...
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
--
D

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
---
"D Boyles" wrote:
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.

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.



"Fred Smith" wrote:

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.

"D Boyles" wrote in message
...
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
--
D

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!!!
--
D


"Max" wrote:

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
---
"D Boyles" wrote:
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.

You're welcome. Thanks for the feedback.

Regards,
Fred.

"D Boyles" wrote in message
...
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!!!
--
D


"Max" wrote:

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
---
"D Boyles" wrote:
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.

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

Regards,
Fred.

"Neil W" wrote in message
...

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.



"Fred Smith" wrote:

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.

"D Boyles" wrote in message
...
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
--
D

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
---
"D Boyles" wrote:
Thank you both Max & Fred,

I was finally able to get the calculations 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!!!
--
D