countif

[jasman]

Hi. I have f4 columns with "TRUE" & "FALSE" values in them. I am tryign to do a countif at the bottom of each to see how many "TRUE" vales there are in each column. Im using formula below but it only works for one of the columns and wont count the "TRUE"s in the other ...just gives answer as 0?

=COUNTIF(O7:O122,"TRUE")

 

[jasman]

i think it is the fact that i have a countif formula in column O but not in the others as I put the countif formula in column N and it worked. is there anyway around this. below are the forumlas used in each column:

column n: =IF($F7=$L7,"TRUE",IF($I7=$L7,"TRUE",IF($K7=$L7,"TRUE","FALSE")))

column o: =IF(OR(COUNTIF($L7,$F7&"*"),COUNTIF($L7,$I7&"*"),COUNTIF($L7,$K7&"*")),OR(ISNUMBER(SEARCH({0,1,2,3,4,5,6},$L7))))

column p: =IF(N7="TRUE","FALSE",IF(LEFT(F7,1)=LEFT(L7,1),"FALSE",IF(LEFT(I7,1)=LEFT(L7,1),"FALSE",IF(LEFT(K7,1)=LEFT(L7,1),"FALSE",IF(LEFT(R7,1)=LEFT(L7,1),"TRUE",IF(LEFT(U7,1)=LEFT(L7,1),"TRUE","FALSE"))))))


column q: =IF(LEFT(F7,1)=LEFT(L7,1),"FALSE",IF(LEFT(I7,1)=LEFT(L7,1),"FALSE",IF(LEFT(K7,1)=LEFT(L7,1),"FALSE",IF(LEFT(R7,1)=LEFT(L7,1),"FALSE",IF(LEFT(R7,1)=LEFT(L7,1),"FALSE","TRUE")))))

 

[jasman]

yes ive tried changing the true to false and still doesnt work

 

[njimack]

Those formulae could/should be contructed a lot more efficiently. Your formula in column N should be:
=OR($F7=$L7,$I7=$L7,$K7=$L7)

As long as your formulae returns the value TRUE or FALSE (as opposed to the string "TRUE" or "FALSE"), your COUNTIF formula will work.

 

[jasman]

i swaitched to the formula you gave for column N and the countif works. Is this the only way to amend the formula?

thanks for help

 

[hyat]

For columns p and q, you could try using:
=COUNTIF(P7:P122,"*TRUE")
=COUNTIF(Q7:Q122,"*TRUE")

 

[njimack]

Those formulae could/should be contructed a lot more efficiently. Your formula in column N should be:
=OR($F7=$L7,$I7=$L7,$K7=$L7)

As long as your formulae returns the value TRUE or FALSE (as opposed to the string "TRUE" or "FALSE"), your COUNTIF formula will work.

You just need to make sure that your formulae are returning TRUE or FALSE without quotes

 

[jasman]

your all geniuses, that works! :D

 

[njimack]

Glad it worked. Thanks for the feedback.