Count repeated word

[skhou]

Hi,

I'm just trying to create a formula where for a given column it will only return a value say "X", if a specific "word" (e.g Rob) is repeated more then 3 times immediately in a row below or above it, otherwise return "A". For example:

Rob -X
Rob -X
Rob -X
Rob -X
Janet -A
Janet -A
Rob -A
Rob -A

I tried the countif formula but that counts every instance of that word in the entire column whereas I only want to count it if it is appears immediately in the row below or above it.

thanks

 

[jtakw]

Hi,

If you can have 2 empty rows:

Book1
	A	B
1
2
3	Rob	X
4	Rob	X
5	Rob	X
6	Rob	X
7	Janet	A
8	Janet	A
9	Rob	A
10	Rob	A
Sheet244

Cell Formulas
Range	Formula
B3	=IF(OR(COUNTIF(A1:A3,A3)=3,COUNTIF(A3:A5,A3)=3),"X","A")

Formula copied down.

 

[skhou]

Hello,

Thank you for that. And if I wanted to check if it was repeated 5 times then I just change the formula to =5?

Regards,

skhou

 

[Peter_SSs]

Here is a way that does not require the extra blank row(s) at the top - it does require 1 (header?) row only.
Make sure the $1000 is something well past the end of your data.
Just change the single 3 to check for a different number of repeats.

Excel Workbook
	A	B
1	Data
2	Rob	X
3	Rob	X
4	Rob	X
5	Rob	X
6	Janet	A
7	Janet	A
8	Rob	A
9	Rob	A
Check Multiples