Forum Discussion

JORDAN BITTICKS's avatar
JORDAN BITTICKS
Copper Contributor
Aug 03, 2018

Formula help If(Countif for multiple range)

I am trying to write a formula to count  a singular "1" if any number of No's appears within 2 distinct ranges.   So if no in B30, and/or B33:37 need to count as 1 what I have now is.    =IF(Coun...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 03, 2018

Hi Jordan,

 

As for formula question you may use

=(Countif('Sheet 18'!B33:B37,"no")>0)*('Sheet 18'!B30="no")
  • JORDAN BITTICKS's avatar
    JORDAN BITTICKS
    Copper Contributor
    Aug 03, 2018

    Hi Sergei! I appreciate your assistance. I tried your formula, but unfortunately I am finding that it is not working. Perhaps you can help me see what I have done wrong? 

    please and thank you! 

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 03, 2018

      Jordan, perhaps I misunderstood your logic. You need 1 then both B30, B33:B37 have "no", or only one of them or what?

      • JORDAN BITTICKS's avatar
        JORDAN BITTICKS
        Copper Contributor
        Aug 06, 2018

        Hi Sergei -- 

         

        I would have needed both -- I apologize for the confusion. I found the source of my error came down to order of operations (silly mistake on my part - I needed to close both operations inside the a parentheses so they functioned together rather than a separately ). This was the final equation that I needed.

        If(countif('Sheet 1'!C33:C37,"no"),"1","0"+If(countif('Sheet 1'!C30,"no"),"1","0"))

         

        I appreciate the time and attention you paid to my problem! 

Resources