Calculate true/false value based on array text input

[Christian]

Hi NG,

I'm struggeling a bit with this task. I have two columns with data, like

A TRUE
A FALSE
B FALSE
C TRUE

I need a formula in a 3rd column that calculates a value of TRUE/FALSE
considering the existing values A, B and C. The result should be like this:

A TRUE FALSE
A FALSE FALSE
B FALSE FALSE
C TRUE TRUE

A: Is always FALSE because there is one record with the value FALSE
B: Is FALSE as there is only one value with FALSE
C: Is TRUE

I have look at sumproduct(), sum() and sumif() but I quite can't get the
formula right.

Basically what should happen should be all the A values are considered
for rows of data with A. The TRUE * FALSE = FALSE, TRUE*TRUE = TRUE and
FALSE*FALSE=FALSE.

How the heck should I do this? Any suggestions are warmly welcome
- Chr

 

[T. Valko]

Maybe this:

=SUMPRODUCT(--(A$1:A$4=A1),--B$1:B$4)=COUNTIF(A$1:A$4,A1)

Copied down

Biff

 

[Christian]

Bingo! That does the trick - thanks!
I would never have thought of putting two "=" in the same formular like
your proposal - I wonder if that's a special array thing.
But it works brilliantly.
Thanks Mr. Valko!
- Chr

 

[T. Valko]

I wonder if that's a special array thing.

No. It's just comapring two different values for equality.

=SUMPRODUCT(--(A$1:A$4=A1),--B$1:B$4)

--B$1:B$4

Converts the TRUE or FALSE to 1 or 0. Then Sumproduct retruns the sum. If
the sum equals the result of the Countif then that condition is TRUE
otherwise, it's FALSE.

For criteria "A" the sum is 1 and the count is 2:

=1=2 = FALSE

Biff