Hi- First time poster here-

I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

Any assistance here would be greatly appreciated.

Thanks in advance

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Unfortuantely that formula doesn't seem to work on alpha cells, although your
"Index" suggestion might be something that I can use to work my way towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Using sumproduct this way has nothing to do with sumproduct as portrayed in
the help, Harlan's formula will simply check that only Joe is in this range
and not Joe and Mary and it will return TRUE if only one unique value exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells, although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

My apologies- you are absolutely correct. I just redid the formula found a
problem with my sytax. Once I corrected it, the formula worked perfectly.
Thank you boh for your help.

Sincerely,
Greg

"Peo Sjoblom" wrote:

Using sumproduct this way has nothing to do with sumproduct as portrayed in
the help, Harlan's formula will simply check that only Joe is in this range
and not Joe and Mary and it will return TRUE if only one unique value exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells, although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Greg"
wrote in message
...
My apologies- you are absolutely correct. I just redid the formula found a
problem with my sytax. Once I corrected it, the formula worked perfectly.
Thank you boh for your help.
Sincerely,
Greg

"Peo Sjoblom" wrote:
Using sumproduct this way has nothing to do with sumproduct as portrayed in
the help, Harlan's formula will simply check that only Joe is in this range
and not Joe and Mary and it will return TRUE if only one unique value exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells, although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)