Return if all Excel column values don't agree

I have the following data in an Excel Spreadsheet:

    A  B  C
 1  b  b  b

I would like to put a formula in the column after the data which compares each (text) value in the row, and returns TRUE if they're all the same ie A=B=C. I w开发者_如何学JAVAould like it to return FALSE if one or more of the values don't agree. ie

    A  B  C  D
 1  b  b  b  TRUE
 2  b  e  b  FALSE
 3  e  b  b  FALSE

I'm aware of logical functions like AND, hence could construct something like

AND(A1=B1,A1=C1.. etc), however, this soon gets unwieldy as soon as the number of columns increases.

Restructuring the data isn't do-able.

Does anyone know an OOTB / VB solution?

If the countif finds the same number as the count then they are all the same.

=IF(COUNTIF(A1:C1,A1)=COUNTA(A1:C1),"true","false")

Hope this is what you are looking for, you just need to extend the ranges for however many columns you want to test.

Update

As pointed out in the comment this fails to return the right result if the dataset has blank cells.

This will return false even if there is a blank cell in the range:

=IF(AND(COUNTIF(A1:C1;A1)=COUNTA(A1:C1);COUNTBLANK(A1:C1)=0);"true";"false")

Here is a VBA UDF: to make it case sensitive remove Option Compare Text

Option Explicit
Option Compare Text
Public Function AllSame(theRange As Range) As Boolean
    Dim vR As Variant
    Dim vC As Variant

    AllSame = True
    vR = theRange
    If IsArray(vR) Then
        vC = vR(1, 1)
        For Each vC In vR
            If vC <> vR(1, 1) Then
                AllSame = False
                Exit For
            End If
        Next vC
    End If
End Function

Although it will be a bit slower, you can also use an array formula (by pressing Ctrl + Shift + Enter after typing it in the cell):

{=IF(SUM(COUNTIF(A1:C1,A1:C1))=POWER(COUNTA(A1:C1), 2),"true","false")}

This will work even if there are empty cells in the range.

.

UPDATE:
This array formula also works, by making sure that there's only 1 unique value in a range. It also still returns an accurate value, even if there are blanks in the range:

{=SUM(IFERROR(1 / COUNTIF(A2:C2,A2:C2), 0)) = 1}