Two formula questions...

G

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
 
B

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)
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top