Array formula based on text and dates..
 

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

Array formula based on text and dates..
 

=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" wrote in message
...
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

Array formula based on text and dates..
 

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" wrote:

=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" wrote in message
...
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

Array formula based on text and dates..
 

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)



"GD" wrote in message
...
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" wrote:

=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" wrote in message
...
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