'All cells equal' check

[steev_jd]

Hi All,

Is it possible to write a formula that checks a range of cells all hold the same value?

i.e. if B1-B10 all = 'True'
then B11 =True,
else B11=False

in the above example I would need all ten cells (b1-B10) with True to return a 'True' response

I posted a simialr question earlier, but it wasn't very clear.

Thanks for your time.

[zbor]

Solmething like this:

=IF(COUNTIF(B1:B10, B1)=COUNTA(B1:B10), TRUE, FALSE)

[steev_jd]

Hi - thanks but this doesn't seem to work. It just returns true no matter how I jumble up the values.

To give more detail, I need it to check
if all cells = P,
return true
else return false.

Please note B1 may not hold 'P',

[zbor]

Then this but only for 10 cells

=IF(COUNTIF(B1:B10, B1)=10, TRUE, FALSE)

or

=IF(COUNTIF(B1:B10, "P")=10, TRUE, FALSE)

[NBVC]

Try perhaps:

=SUMPRODUCT((B1:B10<>"")/COUNTIF(B1:B10,B1:B10&""))=1

this will return TRUE if all values are the same in B1:B10 (doesn't matter the value).

If you have blanks within the range and want to return False if any are blank.... then

=AND(COUNTBLANK(B1:B10)=0,SUMPRODUCT((B1:B10<>"")/COUNTIF(B1:B10,B1:B10&""))=1)

[steev_jd]

Excellent guys, thanks very much - this has worked.

[martindwilson]

=frequency(b1:b10,b1:b10)=10 oops ony works for numbers!