Searching large number of cells for a cell containing a different value

I have a number of worksheets each containing 48 columns. There are approximately 1000 rows on each sheet and all cells contain data. E.g.

Port 0/1:UP Port0/2:UP Port 0/3:DOWN ……
Port 0/1:UP Port0/2:UP Port 0/3:DOWN ……
Port 0/1:UP Port0/2:DOWN Port 0/3:DOWN ……
Port 0/1:UP Port0/2:UP Port 0/3:DOWN ……


I simply need to know if all cells in a column contain the same value or if they change at any point.

So I would like to put a formula in the last cell in each column that displays a 1 if all the cells in that column have the word UP (or DOWN but not some of each) in them and a 0 if some of the cells in the column have UP and some have DOWN.

I feel this should not be as difficult as I am clearly making it and would be most grateful for any help.

Regards

ColinB

Hi,

ajust the range B1:B4 to match your data:

=IF(COUNTA(B1:B4)=COUNTIF(B1:B4,"*UP"),1,IF(COUNTA(B1:B4)=COUNTIF(B1:B4,"*DOWN"),1,0))

Try:

=--(OR(COUNTIF(A1:A4,"*UP*")=ROWS(A1:A4),COUNTIF(A1:A4,"*DOWN*")=ROWS(A1:A4)))

where A1:A4 is the range in column A you're looking at....then copy to adjacent columns...

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save