Excel Count Function

[Mary K. Bullock]

I need to create a function for the following example -

I have a column of "p's" and "m's". If there are 5 consecutive "p's", I
want an "r" to display, but if there aren't 5 consecutive "p's", I want the
cell to remain empty...

Any help would be appreciated!

Thanks

 

[Eric]

This formula seemed to work for me. Modify the A5 to be
the cell want to reference. Also, you need to start this
formula on the fifth row of the series you want to count.

=IF(COUNTIF(OFFSET(A5,0,0,-5,1),"p")=5,"r","")

Hope that helps!

Eric

 

[Bernie Deitrick]

Mary:

For values in the range A1:A100, array enter (enter with Ctrl-Shift-Enter)

=IF(SUM((A1:A96&A2:A97&A3:A98&A4:A99&A5:A100="ppppp")*1)>0,"r","")

Nota Bene - each of the ranges is 4 cells less than the full range,
progressively offset by one cell.

HTH,
Bernie
MS Excel MVP