countif on unformatted dates

[[email protected]]

I have few dates in column A but it starts with an apostrophe sign.
Lets say the range is A1:A9 and here is a sample data
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/5/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/6/2006 11:35:08 AM
'6/7/2006 11:35:08 AM
'6/8/2006 11:35:08 AM
'6/9/2006 11:35:08 AM
'6/9/2006 11:35:08 AM

I have the following values in cell A11:A15
6/5/2006
6/6/2006
6/7/2006
6/8/2006
6/9/2006

If I do a countif in cell B11 =COUNTIF(A1:A9,A11), the answer I get is
ZERO

How come???

I know it has got something to do with formats but not sure what. What
would be the correct formula?

[David Biddulph]

<[email protected]> wrote in message
news:[email protected]...
>I have few dates in column A but it starts with an apostrophe sign.
> Lets say the range is A1:A9 and here is a sample data
> '6/5/2006 11:35:08 AM
> '6/5/2006 11:35:08 AM
> '6/5/2006 11:35:08 AM
> '6/6/2006 11:35:08 AM
> '6/6/2006 11:35:08 AM
> '6/7/2006 11:35:08 AM
> '6/8/2006 11:35:08 AM
> '6/9/2006 11:35:08 AM
> '6/9/2006 11:35:08 AM
>
> I have the following values in cell A11:A15
> 6/5/2006
> 6/6/2006
> 6/7/2006
> 6/8/2006
> 6/9/2006
>
> If I do a countif in cell B11 =COUNTIF(A1:A9,A11), the answer I get is
> ZERO
>
> How come???
>
> I know it has got something to do with formats but not sure what. What
> would be the correct formula?

Your A1 to A9 are text values, forced to that because of the apostrophe.

Without the apostrophe you can hopefully format the cells as date & time.
--
David Biddulph

[Peo Sjoblom]

One possible way using your example


=SUMPRODUCT(--(INT(--SUBSTITUTE($A$1:$A$9,"'",""))=A11))

copy down will give

3
2
1
1
2


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


<[email protected]> wrote in message
news:[email protected]...
>I have few dates in column A but it starts with an apostrophe sign.
> Lets say the range is A1:A9 and here is a sample data
> '6/5/2006 11:35:08 AM
> '6/5/2006 11:35:08 AM
> '6/5/2006 11:35:08 AM
> '6/6/2006 11:35:08 AM
> '6/6/2006 11:35:08 AM
> '6/7/2006 11:35:08 AM
> '6/8/2006 11:35:08 AM
> '6/9/2006 11:35:08 AM
> '6/9/2006 11:35:08 AM
>
> I have the following values in cell A11:A15
> 6/5/2006
> 6/6/2006
> 6/7/2006
> 6/8/2006
> 6/9/2006
>
> If I do a countif in cell B11 =COUNTIF(A1:A9,A11), the answer I get is
> ZERO
>
> How come???
>
> I know it has got something to do with formats but not sure what. What
> would be the correct formula?
>

[[email protected]]

Thank you very much

Peo Sjoblom wrote:
> One possible way using your example
> =SUMPRODUCT(--(INT(--SUBSTITUTE($A$1:$A$9,"'",""))=A11))