if multiple values same/others different, return true

[Guest]

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

 

[Guest]

=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

 

[Guest]

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:

 

[Guest]

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

=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.

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

 

[Guest]

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

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

=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.

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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