On Mon, 26 Apr 2010 15:58:59 +0100, Barny
wrote:


I have a spreadsheet with 14 columns and 20 rows - and what I would like
is a formula that will look in a particlular row i.e B1:B14 and return
and X in cell B15 if consequtive cells of 4 or more initials are found
e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would be X
and if c3:c4:c5:c6 = MM the result in B16 would be X. If however
B1:B2:B3 = TT and B4 = NN then no X is returned (cells will either
contain initials or be blank)

is this possible?

I think you are mixing columns and rows. B1:B14 is part of a column,
not a row.

Assuming that you have data i 14 rows and 20 columns, in the range
A1:T14 and that you what the X's on row 15, i.e. on the row just below
your data.

Try the following formula in cell A15:

=IF(SUMPRODUCT(--(A1:A11<""),--(A1:A11=A2:A12),--(A2:A12<""),--(A2:A12=A3:A13),--(A3:A13<""),--(A3:A13=A4:A14))0,"X","")

Copy the formula to the right to cover A15:T15.

The result is that you will have an "X" on row 15 if there are at
least one occurance of consecutive cells in rows 1 to 14 in the
corresponding column that have the same non-blank value.

Hope this helps / Lars-Åke