Excel Formula Question 2

LupoLover · 4 Nov 2014 at 08:19

Sorry guys, thought I better start a new thread as no one will read once other one solved (Many Thanks)

I have a colunm with loads of TRUE or FALSE in, and the same in another so say column A and B. I want a cell that will count which one has most TRUE and put a A or B in to say which has most. I know I got to use COUNT but I spent 2 hours messing around last night and cant get anything to work.

Cheers

Sheldon

john_s · 4 Nov 2014 at 08:32

Something like

Code:

=IF(COUNTIF(A1:A10, "TRUE")>COUNTIF(B1:B10, "TRUE"),"YES", "")

AJK · 4 Nov 2014 at 08:32

Try COUNTIF.

LupoLover · 4 Nov 2014 at 09:34

Perfect, cheers guys

LupoLover · 5 Nov 2014 at 08:39

Oki guys I know Im a pain but this one I dont think is my fault!

I have in AQ9 : =AND(AP9=TRUE,AM9=TRUE)

AP9 and AM9 have formula in that then display TRUE.

Why does AQ9 still show FALSE?

james.miller · 5 Nov 2014 at 10:11

make sure your TRUE and FALSEs are consistent, and by that i mean if you are checking for TRUE or FALSE then make sure your formulas are returning TRUE or FALSE and not "TRUE" or "FALSE" because they are two different things; "TRUE"/"FALSE" are strings and TRUE/FALSE are boolean values.

john_s · 5 Nov 2014 at 13:39

As above, try wrapping the TRUEs in your formula in double quotes.

HeX · 6 Nov 2014 at 12:34

As above, it's because in your IF statements you've returned "TRUE" as text rather than a boolean value.

If you change your IF statement to just return TRUE (no quotation marks) then your AND formula will work.

Or as others have said you can amend your AND formula to look for the text TRUE.

In a nutshell "TRUE" and TRUE are different things in Excel