Formula help If(Countif for multiple range)

[JORDAN BITTICKS]

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(Countif('Sheet 18'!B33:B37,"no"),"1","0")+IF(Countif('Sheet 18'!B30,"no"),"-1","0")+IF(Countif('Sheet 17'!B33:B37,"no"),"1","0")+IF(Countif('Sheet 17'!B30,"no"),"-1","0") .... etc etc

The issue here is that if there is a "no" in 33 and a "no" in 30 it counts 2.  <Or> functions do not appear to work because range is in same column and criteria is the same too. 

Example:  I am reviewing if each of 18 students has packed correctly for a trip. Each student has a checklist which will feed into a summary sheet that tells me how many children got a "no" in the section 30 and 33:37 (excluding line 31 and 32 ).

I cannot change the checklist or the order of questions. In the image the formula counts this as 3 students instead of 2 that are missing multiple items.

My workbook is getting bogged down w 20 checklists each containing data validation drop downs (yes or no's) in addition to the linked data / formulas across 

I have looked into array formulas as well as pivot tables but I fear those options will bog down the workbook even more, complicate things for my team,  as well as deviate from controlled format of these documents. Basically its gotta be a formula and I cant change the format/ order of questions

The concept here is simple, and I know its out there but I am totally stuck. 

pls and thank you!!! 

[#2]

Hi Jordan,

As for formula question you may use

=(Countif('Sheet 18'!B33:B37,"no")>0)*('Sheet 18'!B30="no")

[#3]

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! 

[#4]

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

[#5]

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! 

[#6]

Hi Jordan,

Here and attach is set of formulas we have

Your one returns "1" if "no" was found in C33:C37, otherwise

"0"+IF(COUNTIF(C30,"no"),"1","0")

You don't need first "0" plus formula will return text or value depends on combination (Excel transforms numerical texts when try to perform arithmetic operations, e.g. ="5"+"3" gives the same result as =5+3, i.e. 8). To simplify, it'll be nested IF

=IF(COUNTIF(C34:C38,"no"),1,IF(COUNTIF(C31,"no"),1,0))

Same result with

=MIN((COUNTIF($C$33:$C$37,"no")>0)+($C$30="no"),1)

Both return 1 if we have one or two "no", otherwise 0.

Previous formula returns 1 if both "no", otherwise 0.