If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Countif using cell refences
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 |
#2
|
|||
|
|||
Countif using cell refences
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 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 |
#3
|
|||
|
|||
Countif using cell refences
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 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 |
#4
|
|||
|
|||
Countif using cell refences
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 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 . |
#5
|
|||
|
|||
Countif using cell refences
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" wrote in message ... 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 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 . |
Thread Tools | |
Display Modes | |
|
|