Array formula based on text and dates..

[GD]

I'm using the following to pull out the amount of cells in the range L3 -
L260 with the phrase 'GD (KT) in them, whithin the month of January.
=SUM(IF(MOD(ROW('2008 Errors'!$A$3:$A$3000),10)=3,IF((YEAR('2008
Errors'!$A$3:$A$3000)=2008)*(MONTH('2008 Errors'!$A$3:$A$3000)=1)*
(LEN('2008 Errors'!I3:I3000)-LEN(SUBSTITUTE('2008 Errors'!I3:I3000,"GD
(KT)",""))>0),1)))
I'm looking for something broader, essentially i'd like to apply the same
principle but for all the cells in the range with the (KT) text occurance,
regarldess of the GD prefix (i.e the formula when applied to 4 cells with the
following data "GD (KT)" "JK (KT)" "ZE (EL)" "AG (KT)" would provide a result
of 3, drawing on the three cells with the (KT) in them. So essentially i'm
looking to relax the text criteria to a phrase as opposed to a set piece of
text...
I'm looking for a version based on the above formula which can be editted to
suit month, (by changing the MONTH criteria) and also just one for counting
the text occurance of KT in the range of cells in general
Cheers

 

[Bob Phillips]

=SUM(IF(MOD(ROW('2008 Errors'!$A$3:$A$30),10)=3,
IF((TEXT('2008 Errors'!$A$3:$A$30,"mmm yyyy")="Jan 2008")*
(ISNUMBER(FIND("(KT)",'2008 Errors'!I3:I30))),1)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[GD]

Hi Bob, thanks for the response - for some reason this produces a result fo
three, but the range of cells I3-L3000 (in increments of 10) are populated
with at least 25 text arrangements with (KT) in them, any ideas?

 

[Bob Phillips]

GD,

I truncated your ranges for my test, maybe it is that. Try this

=SUM(IF(MOD(ROW('2008 Errors'!$A$3:$A$3000),10)=3,
IF((TEXT('2008 Errors'!$A$3:$A$3000,"mmm yyyy")="Jan 2008")*
(ISNUMBER(FIND("(KT)",'2008 Errors'!I3:I3000))),1)))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)