SOLVED
Home

=IF Function, I think

%3CLINGO-SUB%20id%3D%22lingo-sub-363200%22%20slang%3D%22en-US%22%3E%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363200%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20here%20is%20my%20problem%2C%20lets%20start%20with%20what%20i%20have%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(C3%2C%22%26gt%3B%22%22%22)%2CIF(AND(X3%3DFALSE%2CY3%3DFALSE%2CZ3%3DFALSE%2CAA3%3DFALSE%2CAB3%3DFALSE%2CAC3%3DFALSE)%2C%22Risk%22%2CIF(AND(X3%3DTRUE%2CY3%3DTRUE%2CZ3%3DTRUE%2CAA3%3DTRUE%2CAB3%3DTRUE%2CAC3%3DTRUE)%2C%22No%20Risk%22%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20of%20which%20is%2C%20if%20C3%20is%20greater%20than%20null%2C%20then%20we%20need%20to%20look%20at%20the%20next%20part%2C%20so%20if%20X3%2CY3%2CZ3%2CAA3%2CAB3%2CAC3%20are%20all%20false%20then%20it%20should%20result%20in%20this%20cell%20showing%20Risk%2C%20but%20if%20any%20of%20the%20cells%20X3%2CY3%2CZ3%2CAA3%2CAB3%2CAC3%20are%20true%20then%20No%20Risk%20would%20show.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20the%20two%20parts%20work%20separately%2C%20but%20I%20just%20cannot%20find%20a%20way%20to%20join%20the%20Countif%20and%20the%20IF%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364066%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364066%22%20slang%3D%22en-US%22%3EYou're%20very%20much%20welcome!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364064%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364064%22%20slang%3D%22en-US%22%3E%3CP%3EWorks%20a%20treat%2C%20thank%20you%20for%20both%20your%20time%20and%20effort%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364063%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364063%22%20slang%3D%22en-US%22%3E%3CP%3EWorks%20a%20treat%2C%20thank%20you%20very%20much%20for%20your%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363703%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363703%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DIF((C3%3D%22%22)%2BSUMPRODUCT(--X3%3AAC3)%2C%3CBR%20%2F%3E%E2%80%9CNo%20Risk%E2%80%9D%2C%3CBR%20%2F%3E%E2%80%9CRisk%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363701%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363701%22%20slang%3D%22en-US%22%3EWhat%20result%20does%20the%20formula%20return%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363698%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363698%22%20slang%3D%22en-US%22%3E%3CP%3ETwifoo%2C%20thank%20you%2C%20but%20please%20replace%20now%20with%20NOT%2C%20sorry%20my%20mistake%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363692%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363692%22%20slang%3D%22en-US%22%3EIf%20the%20formula%20returns%20your%20desired%20result%2C%20then%20that%20should%20be%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363690%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363690%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20you%20continued%20help%2C%20but%20still%20now%20working%2C%20anything%20you%20can%20think%20of%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363338%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363338%22%20slang%3D%22en-US%22%3EGiven%20that%20X3%3AAC3%20is%20a%20contiguous%20range%2C%20the%20formula%20can%20be%20reduced%20to%20this%3A%3CBR%20%2F%3E%3DIF(ISBLANK(C3)%2BSUMPRODUCT(%E2%80%94X3%3AAC3)%2C%3CBR%20%2F%3E%E2%80%9CNo%20Risk%E2%80%9D%2C%3CBR%20%2F%3E%E2%80%9CRisk%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363328%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363328%22%20slang%3D%22en-US%22%3EThe%20last%20argument%20should%20be%20%E2%80%9CRisk%E2%80%9D%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363325%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363325%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DIF(ISBLANK(C3)%2BX3%2BY3%2BZ3%2BAA3%2BAB3%2BAC3%2C%3CBR%20%2F%3E%E2%80%9CNo%20Risk%E2%80%9D%2C%3CBR%20%2F%3ERisk%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363289%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363289%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Twifoo%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20help%2C%20much%20simpler%20than%20my%20way%2C%20but%20comes%20back%20with%20a%20%23value!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20guessing%20that%2C%20that%20because%20although%20looking%20at%20X3-AC3%2C%20it%20is%20not%20looking%20for%20a%20True%20or%20False%20statement%20against%20them.%26nbsp%3B%20In%20this%20case%2C%20True%20would%20mean%20that%20if%20any%20one%20of%20the%26nbsp%3BX3%20to%20AC3%20are%20true%2C%20then%20that%20would%20%3DNo%20Risk%2C%20and%20a%20false%20to%20X3%20to%20AC3%20would%20%3D%20Risk%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363276%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20Function%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363276%22%20slang%3D%22en-US%22%3EHello%20Andy%2C%3CBR%20%2F%3EI%20guess%20that%20this%20is%20what%20you%20need%3A%3CBR%20%2F%3E%3DIF(C3%3D%E2%80%9C%E2%80%9D%2BX3%2BY3%2BZ3%2BAA3%2BAB3%2BAC3%2C%3CBR%20%2F%3E%E2%80%9CNo%20Risk%E2%80%9D%2C%3CBR%20%2F%3E%E2%80%9CRisk%E2%80%9D)%3C%2FLINGO-BODY%3E
Andy_Snow
Occasional Contributor

So here is my problem, lets start with what i have;

=COUNTIF(C3,">"""),IF(AND(X3=FALSE,Y3=FALSE,Z3=FALSE,AA3=FALSE,AB3=FALSE,AC3=FALSE),"Risk",IF(AND(X3=TRUE,Y3=TRUE,Z3=TRUE,AA3=TRUE,AB3=TRUE,AC3=TRUE),"No Risk",""))

 

The idea of which is, if C3 is greater than null, then we need to look at the next part, so if X3,Y3,Z3,AA3,AB3,AC3 are all false then it should result in this cell showing Risk, but if any of the cells X3,Y3,Z3,AA3,AB3,AC3 are true then No Risk would show.

 

I know that the two parts work separately, but I just cannot find a way to join the Countif and the IF functions.

13 Replies
Hello Andy,
I guess that this is what you need:
=IF(C3=“”+X3+Y3+Z3+AA3+AB3+AC3,
“No Risk”,
“Risk”)

Hi Twifoo

Many thanks for your help, much simpler than my way, but comes back with a #value!

 

I am guessing that, that because although looking at X3-AC3, it is not looking for a True or False statement against them.  In this case, True would mean that if any one of the X3 to AC3 are true, then that would =No Risk, and a false to X3 to AC3 would = Risk 

Try this:
=IF(ISBLANK(C3)+X3+Y3+Z3+AA3+AB3+AC3,
“No Risk”,
Risk”)
The last argument should be “Risk”
Given that X3:AC3 is a contiguous range, the formula can be reduced to this:
=IF(ISBLANK(C3)+SUMPRODUCT(—X3:AC3),
“No Risk”,
“Risk”)

Thanks for you continued help, but still now working, anything you can think of?

 

If the formula returns your desired result, then that should be it.

Twifoo, thank you, but please replace now with NOT, sorry my mistake

 

What result does the formula return?
Solution
Try this:
=IF((C3="")+SUMPRODUCT(--X3:AC3),
“No Risk”,
“Risk”)

Works a treat, thank you very much for your time.

Works a treat, thank you for both your time and effort

You're very much welcome!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies