Two formula questions...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GD - 08 Jan 2008 15:57 GMT

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

Reply to this Message

CLR - 08 Jan 2008 16:26 GMT

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

> 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
[quoted text clipped - 12 lines]
>
> Cheers

Reply to this Message

Bob Phillips - 08 Jan 2008 17:16 GMT

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

Signature

---
HTH

Bob

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

> Question number one: (and this is a simple one I hope) were I wanting to
> do a
[quoted text clipped - 16 lines]
>
> Cheers

Reply to this Message

MartinW - 09 Jan 2008 01:54 GMT

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

> Question number one: (and this is a simple one I hope) were I wanting to
> do a
[quoted text clipped - 16 lines]
>
> Cheers

Reply to this Message