Making 'True' =1 "false" =0

[Guest]

Is there a setting that will automatically apply these at the moment I have
to apply an if statement before I can get any calculations done.

=SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$5>0) gives a result of 0 even though it
shows
=SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE})

whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$5>0,1,0)) gives
the results of 1 with the array of
=SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1})

It seems to treat the trues and falses as text rather than values.

I do not want to have to put the if statement in.

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$5>0))

 

[Ron Rosenfeld]

Is there a setting that will automatically apply these at the moment I have
to apply an if statement before I can get any calculations done.

=SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$5>0) gives a result of 0 even though it
shows
=SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE})

whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$5>0,1,0)) gives
the results of 1 with the array of
=SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1})

It seems to treat the trues and falses as text rather than values.

I do not want to have to put the if statement in.

=SUMPRODUCT(($C$4:$I$4=C4)*($C$5:$I$5>0))

or

=SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$5>0))
--ron

 

[Guest]

Thank you , I assume that the first - somehow multiplies by - 1 and the
second one reverses it to make it positive again.

 

[T. Valko]

I assume that the first - somehow multiplies by - 1

I don't know how Excel does this internally but it "coerces" the booleans
first to negative numbers and then to positive numbers. Does it multiply
by - 1, that I don't know.

 

[David Biddulph]

Yes.
--
David Biddulph

Thank you , I assume that the first - somehow multiplies by - 1 and the
second one reverses it to make it positive again.

 

[Bob Phillips]

Unlikely that it multiplies by -1, but it does negate the value, which has
the same effect, and the second negates again, a negative-negative is a
positive.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)