Two formula questions...

[GD]

Question number one: (and this is a simple one I hope) were I wanting to do a
COUNTA to concentrate on specific cells in increments of 10 and missing out
those between, I.e A1, A11, A21 and so on...how would I do this, without
overloading the formula as this needs to be repeated from 1-3000 to
accomodate a years worth of data
Question number two: were I using a complex formula to produce a result for
cells which first qualify one criteria (Having the phrase 'GD (KT)' in it)
and then correspond to a date in another: example being
{=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)))}
Would there be a way of doing this with a NON specific phrase. I.e all cells
with (KT) in them, regardless of the GD prefix (or any other)?

Cheers

 

[CLR]

For question 1 you might consider coloring each 10th row and then looking
here..

http://www.cpearson.com/Excel/colors.htm

or, using a 1-10 numbered helper column and Autofiltering the 10th rows and
using a SUBTOTAL formula


Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Q1. =SUMPRODUCT(--(MOD(ROW(A1:A3000),10)=1),A1:A3000)

Q2. =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,"(KT)",""))>0),1)))

still an array formula

--
---
HTH

Bob


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

 

[MartinW]

Another possibility for question 1 is to extract every
tenth value to a new helper column.
Put this in B1
=INDEX(A:A,ROW()*10-9)
and drag down as far as is needed.

if you need to handle blanks you can use
=IF(INDEX(A:A,ROW()*10-9)="","",INDEX(A:A,ROW()*10-9))

HTH
Martin