Excel Count Function

  • Thread starter Thread starter Mary K. Bullock
  • Start date Start date
M

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
 
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
 
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
 
Back
Top