Forum Discussion
JORDAN BITTICKS
Aug 03, 2018Copper Contributor
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...
SergeiBaklan
Aug 03, 2018MVP
Hi Jordan,
As for formula question you may use
=(Countif('Sheet 18'!B33:B37,"no")>0)*('Sheet 18'!B30="no")
JORDAN BITTICKS
Aug 03, 2018Copper Contributor
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!
- SergeiBaklanAug 03, 2018MVP
Jordan, perhaps I misunderstood your logic. You need 1 then both B30, B33:B37 have "no", or only one of them or what?
- JORDAN BITTICKSAug 06, 2018Copper Contributor
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!
- SergeiBaklanAug 06, 2018MVP
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.