if multiple values same/others different, return true

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

=IF(SUMIF([YOUR RANGE],1)=40,"TRUE","FALSE")

This returns the sum of all 1s in the range, and if it equals 40, then TRUE
is returned, else FALSE is returned.

Dave
--
Brevity is the soul of wit.


"Jshendel" wrote:

> A challenge:
> I have a list that has 48 columns in each row. Each cell has a score of
> 1-4. I would like a "true" return if specifically and only these cells have
> a 1 and others do not have a 1.
> For example:
> A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> has a different number.
>
> I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
>
> Thank you

Hi Jshendel,

did you try to use

=if(sum(a1:an1)=40,"true","false")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Jshendel" escreveu:

> A challenge:
> I have a list that has 48 columns in each row. Each cell has a score of
> 1-4. I would like a "true" return if specifically and only these cells have
> a 1 and others do not have a 1.
> For example:
> A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> has a different number.
>
> I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
>
> Thank you

I thought of this, too.
But, if cell A1 is a 2 and D1 is a 1, your equation will still return true,
when it is false.
Thanks anyway,
Josh

"Dave F" wrote:

> =IF(SUMIF([YOUR RANGE],1)=40,"TRUE","FALSE")
>
> This returns the sum of all 1s in the range, and if it equals 40, then TRUE
> is returned, else FALSE is returned.
>
> Dave
> --
> Brevity is the soul of wit.
>
>
> "Jshendel" wrote:
>
> > A challenge:
> > I have a list that has 48 columns in each row. Each cell has a score of
> > 1-4. I would like a "true" return if specifically and only these cells have
> > a 1 and others do not have a 1.
> > For example:
> > A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> > D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> > Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> > has a different number.
> >
> > I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
> >
> > Thank you

The equation I give only sums cells whose value is 1. It ignores 2.
--
Brevity is the soul of wit.


"Jshendel" wrote:

> I thought of this, too.
> But, if cell A1 is a 2 and D1 is a 1, your equation will still return true,
> when it is false.
> Thanks anyway,
> Josh
>
> "Dave F" wrote:
>
> > =IF(SUMIF([YOUR RANGE],1)=40,"TRUE","FALSE")
> >
> > This returns the sum of all 1s in the range, and if it equals 40, then TRUE
> > is returned, else FALSE is returned.
> >
> > Dave
> > --
> > Brevity is the soul of wit.
> >
> >
> > "Jshendel" wrote:
> >
> > > A challenge:
> > > I have a list that has 48 columns in each row. Each cell has a score of
> > > 1-4. I would like a "true" return if specifically and only these cells have
> > > a 1 and others do not have a 1.
> > > For example:
> > > A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> > > D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> > > Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> > > has a different number.
> > >
> > > I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
> > >
> > > Thank you

Something along these lines:

SUM your 40 cells

Test your 8 cells in the OR function

=IF(AND(SUM(A2:C2,E2,G2:H2 ...etc)=6,OR(D2>1,F2>1,J2>1.etc)),"TRUE","FALSE")

HTH

"Jshendel" wrote:

> A challenge:
> I have a list that has 48 columns in each row. Each cell has a score of
> 1-4. I would like a "true" return if specifically and only these cells have
> a 1 and others do not have a 1.
> For example:
> A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> has a different number.
>
> I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
>
> Thank you

It works.
However, in my own boredom, I made my own equation. Long and ugly, but it
works, too.
=IF(AND(BE6=BF6=BG6=BH6=BI6=BJ6=BK6=BL6=BM6=BN6=BO6=BP6=BQ6=BR6=BS6=BT6=BU6=BV6=BW6=BX6=BY6=BZ6=CA6=CB6=CC6=CD6=CO6=CP6=CQ6=CR6=CS6=CT6=CU6=CV6=CW6=CX6=CY6=CZ6,(OR(BE6<>CF6,BE6<>CG6,BE6<>CH6,BE6<>CI6,BE6<>CJ6,BE6<>CK6,BE6<>CK6,BE6<>CL6,BE6<>CM6,BE6<>CN6))),TRUE,FALSE)

Thanks everyone,
Josh


"Toppers" wrote:

> Something along these lines:
>
> SUM your 40 cells
>
> Test your 8 cells in the OR function
>
> =IF(AND(SUM(A2:C2,E2,G2:H2 ...etc)=6,OR(D2>1,F2>1,J2>1.etc)),"TRUE","FALSE")
>
> HTH
>
> "Jshendel" wrote:
>
> > A challenge:
> > I have a list that has 48 columns in each row. Each cell has a score of
> > 1-4. I would like a "true" return if specifically and only these cells have
> > a 1 and others do not have a 1.
> > For example:
> > A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> > D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> > Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> > has a different number.
> >
> > I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
> >
> > Thank you

This will not work because a true formula can equal anything from 49 (47 "1"
and 1 "2" up to 72 (40 "1" and 8 "4")
and if a number <>1 is in the cell that should be a 1, it will return true
when it is false.
Thanks for you advice,
Josh

"Marcelo" wrote:

> Hi Jshendel,
>
> did you try to use
>
> =if(sum(a1:an1)=40,"true","false")
>
> hth
> --
> regards from Brazil
> Thanks in advance for your feedback.
> Marcelo
>
>
>
> "Jshendel" escreveu:
>
> > A challenge:
> > I have a list that has 48 columns in each row. Each cell has a score of
> > 1-4. I would like a "true" return if specifically and only these cells have
> > a 1 and others do not have a 1.
> > For example:
> > A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
> > D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
> > Basically I have 40 cells that need to be a 1 and 8 cells that at least one
> > has a different number.
> >
> > I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.
> >
> > Thank you