Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif on unformatted dates
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif on unformatted dates
wrote in message
ups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif on unformatted dates
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 wrote in message ups.com... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif on unformatted dates
Thank you very much
Peo Sjoblom wrote: One possible way using your example =SUMPRODUCT(--(INT(--SUBSTITUTE($A$1:$A$9,"'",""))=A11)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using countif to add dates | Excel Discussion (Misc queries) | |||
Countif on multiple dates | Excel Worksheet Functions | |||
COUNTIF using Dates | Excel Worksheet Functions | |||
Countif using dates | Excel Worksheet Functions | |||
COUNTIF With Multiple Dates, Columns and Text | Excel Worksheet Functions |