i would like to add a formula into my spreadsheet that counts if a date in a
previous cell appears, but the date is constantly being changed so therefore
i would like the formula to contain a cell reference instead of the date,
other problem is that the date will be encoded in other data, therefore i
need to wild card it at the beggining and the end with *'s but it doesnt seem
to want to work with a cell reference and not a constant number

Show us the formula you are currently using.

Add a few cell references and cell data to go along with it.


Gord Dibben MS Excel MVP

On Sat, 10 Apr 2010 12:48:01 -0700, Gnealeuk
<(E-Mail Removed)> wrote:

>i would like to add a formula into my spreadsheet that counts if a date in a
>previous cell appears, but the date is constantly being changed so therefore
>i would like the formula to contain a cell reference instead of the date,
>other problem is that the date will be encoded in other data, therefore i
>need to wild card it at the beggining and the end with *'s but it doesnt seem
>to want to work with a cell reference and not a constant number

Suppose you have a list of data values in A1:A10, where within the
text in each cell is a date-like string. E.g., abcd9/12/2010def
Put in cell C1 the date you want to search for in A1:A10, and format
it in the same format as it appears in the values in A1:A10. For
example, if you have 09/12/2010 somewhere in the strings in A1:A10, C1
must be formatted exactly the same way. It won't work if the formats
are different.

Then, use the following array formula:

=SUM(--(NOT(ISERROR(FIND(T(E1),A1:A10)))))

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sat, 10 Apr 2010 12:48:01 -0700, Gnealeuk
<(E-Mail Removed)> wrote:

>i would like to add a formula into my spreadsheet that counts if a date in a
>previous cell appears, but the date is constantly being changed so therefore
>i would like the formula to contain a cell reference instead of the date,
>other problem is that the date will be encoded in other data, therefore i
>need to wild card it at the beggining and the end with *'s but it doesnt seem
>to want to work with a cell reference and not a constant number

The formula that i am currently using is
=COUNTIF(PremierText!B:B,"*" & 'Weekly reports'!D3:F3& "*")

Premier text column contains data like "01/01/2010 14:15:05" so basically a
date and time mixed together,
as this is automatically put like this from the original source,
whereas weekly reports cells D3:F3 contain just the date os 01/01/2010.

"Gord Dibben" wrote:

> Show us the formula you are currently using.
>
> Add a few cell references and cell data to go along with it.
>
>
> Gord Dibben MS Excel MVP
>
> On Sat, 10 Apr 2010 12:48:01 -0700, Gnealeuk
> <(E-Mail Removed)> wrote:
>
> >i would like to add a formula into my spreadsheet that counts if a date in a
> >previous cell appears, but the date is constantly being changed so therefore
> >i would like the formula to contain a cell reference instead of the date,
> >other problem is that the date will be encoded in other data, therefore i
> >need to wild card it at the beggining and the end with *'s but it doesnt seem
> >to want to work with a cell reference and not a constant number
>
> .
>

Your first problem is you can't use a range for the search criteria.

If D3 contains a *text* date, you can use:
=COUNTIF(PremierText!B:B,"*" & 'Weekly reports'!D3& "*")

If this still doesn't work, then the likely cause is your cells don't
contain text. If D3 contains a date, then you can try:
=COUNTIF(PremierText!B:B,"*"&Text('Weekly reports'!D3,"mm/dd/yyyy")&"*")
[or maybe "dd/mm/yyyy"]

If neither of these work, post back with what's actually in your cells.
There's no reason you can't get this to work.

Regards,
Fred




"Gnealeuk" <(E-Mail Removed)> wrote in message
news:C22E2D66-C55D-460E-9FE7-(E-Mail Removed)...
> The formula that i am currently using is
> =COUNTIF(PremierText!B:B,"*" & 'Weekly reports'!D3:F3& "*")
>
> Premier text column contains data like "01/01/2010 14:15:05" so basically
> a
> date and time mixed together,
> as this is automatically put like this from the original source,
> whereas weekly reports cells D3:F3 contain just the date os 01/01/2010.
>
> "Gord Dibben" wrote:
>
>> Show us the formula you are currently using.
>>
>> Add a few cell references and cell data to go along with it.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Sat, 10 Apr 2010 12:48:01 -0700, Gnealeuk
>> <(E-Mail Removed)> wrote:
>>
>> >i would like to add a formula into my spreadsheet that counts if a date
>> >in a
>> >previous cell appears, but the date is constantly being changed so
>> >therefore
>> >i would like the formula to contain a cell reference instead of the
>> >date,
>> >other problem is that the date will be encoded in other data, therefore
>> >i
>> >need to wild card it at the beggining and the end with *'s but it doesnt
>> >seem
>> >to want to work with a cell reference and not a constant number
>>
>> .
>>