COUNTIF with repeating pattern
 

I am attempting to do a COUNTIF on a long series of values. I need to count
among the first 100 values for all of the 1, 2, 3, 4. Then count among the
next 100 (101-200) for 1,2,3,4. (repeat 25 times) I am having to go into each
formula and type in the range of cells. Is there a faster way to do this
without naming each range before I create the formulas? Given that the
pattern repeats, it should be easy. . . I want to spend as little time as
possible scrolling up and down. Thank you!

COUNTIF with repeating pattern
 

Try this

=COUNTIF(OFFSET($A$1,(INT((ROW()-1)/4)*100),0,100,1),MOD(ROW(),4)+(MOD(ROW()
,4)=0)*4)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vandynd" wrote in message
...
I am attempting to do a COUNTIF on a long series of values. I need to
count
among the first 100 values for all of the 1, 2, 3, 4. Then count among the
next 100 (101-200) for 1,2,3,4. (repeat 25 times) I am having to go into
each
formula and type in the range of cells. Is there a faster way to do this
without naming each range before I create the formulas? Given that the
pattern repeats, it should be easy. . . I want to spend as little time as
possible scrolling up and down. Thank you!

COUNTIF with repeating pattern
 

Assuming you want to count values in column A,

cell B1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),1)
cell C1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),2)
cell D1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),3)
cell E1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),4)

and drag down to row 25

Regards

Trevor


"vandynd" wrote in message
...
I am attempting to do a COUNTIF on a long series of values. I need to count
among the first 100 values for all of the 1, 2, 3, 4. Then count among the
next 100 (101-200) for 1,2,3,4. (repeat 25 times) I am having to go into
each
formula and type in the range of cells. Is there a faster way to do this
without naming each range before I create the formulas? Given that the
pattern repeats, it should be easy. . . I want to spend as little time as
possible scrolling up and down. Thank you!

COUNTIF with repeating pattern
 

Thank you - I'll try both as I've never heard of OFFSET. Meanwhile, I
stumbled into PivotTables and my problem was immediately solved. Wish I'd
known that a few days ago!

Thanks again!

"Trevor Shuttleworth" wrote:

Assuming you want to count values in column A,

cell B1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),1)
cell C1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),2)
cell D1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),3)
cell E1: =COUNTIF(OFFSET($A$1,(ROW()-1)*100,,100),4)

and drag down to row 25

Regards

Trevor


"vandynd" wrote in message
...
I am attempting to do a COUNTIF on a long series of values. I need to count
among the first 100 values for all of the 1, 2, 3, 4. Then count among the
next 100 (101-200) for 1,2,3,4. (repeat 25 times) I am having to go into
each
formula and type in the range of cells. Is there a faster way to do this
without naming each range before I create the formulas? Given that the
pattern repeats, it should be easy. . . I want to spend as little time as
possible scrolling up and down. Thank you!