Check is range is logical

[Orongo]

Hi, I have 3 columns that are either true or false. For every row there can only be one true. I want to create a column that checks this, not using any macro. I understand I can make a nested formula with many ifs, but it seems like there should be a simpler way. Is there?

 

[pvr928]

Hi Orongo

Try this:

=IF(COUNTIF(B2:D2,"TRUE")>1,"Check","Ok")

Where B2:D2 are the three columns are either true or false.

Have fun!

cheers

pvr928

 

[Orongo]

It works prefect! Many thanks!

Hi Orongo

Try this:

=IF(COUNTIF(B2:D2,"TRUE")>1,"Check","Ok")

Where B2:D2 are the three columns are either true or false.

Have fun!

cheers

pvr928

 

[pvr928]

It works prefect! Many thanks!

My pleasure Orongo - glad it was what you were looking for!

Cheers

pvr928

 

[Rick Rothstein]

Try this:

=IF(COUNTIF(B2:D2,"TRUE")>1,"Check","Ok")

Where B2:D2 are the three columns are either true or false.

You should change your "greater than" sign to a "not equal" sign just in case those three cells are all FALSE.

=IF(COUNTIF(B2:D2,"TRUE")<>1,"Check","Ok")

Although I would eliminate the COUNTIF function call and use this simpler construction instead...

=IF(B2+C2+D2=1,"Ok","Check")

 

[pvr928]

You should change your "greater than" sign to a "not equal" sign just in case those three cells are all FALSE.

=IF(COUNTIF(B2:D2,"TRUE")<>1,"Check","Ok")

Although I would eliminate the COUNTIF function call and use this simpler construction instead...

=IF(B2+C2+D2=1,"Ok","Check")

Nice one Rick - I keep forgetting the power of boolean functions!

Cheers

pvr928