Forum Discussion

scottkramer.14's avatar
scottkramer.14
Copper Contributor
Apr 05, 2018

Needing a little help in understanding the logic I am trying to use IF and COUNT IF

Hi all.   I have two separate worksheets, one of which needs to be connected to another.  On  Sheet 1 for column K, I have either "true" or "false".  In Sheet 2 for cell B6, I want to count all the number of "true"s from Sheet 1.  No "false" cells will be counted.  

 

I am sure my logical thinking is off here.   =if('Sheet 1'!K2:K29="true", "Sheet 2"=countif(B6)).   How close did I get to what I wanted to do?  If the formula is wrong, what formula should I have been using?  Thanks very much for your help on this.

Formulas & Functions
tips and tricks
    • scottkramer.14's avatar
      scottkramer.14
      Copper Contributor
      Apr 05, 2018
      Thanks for you suggestion. I tried it and the answer came up 0, which I thought was odd.
      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Apr 05, 2018

        Hi Scott,

         

        I guess that the TRUEs and FALSEs are parsed as texts, and it seems that the COUNTIF function cannot handle this situation!

         

        So please replace the previous formula with this:

        =SUMPRODUCT(--(K:K="TRUE"))

        Hope that helps

Resources