Correct IF, AND syntax

[ddub25]

I am struggling to compose the correct syntax for these IF's, AND's in one formula.

IF B11 >0,
AND result from, =SUMIFS(N5:N16,Q5:Q16,"") returns 0
Then return "h"

IF B11 =0
OR B11 =""
Then return "q"

Can anyone help. Thanks.

Dan

 

[taurean]

Are there any chances that B11 will be negative?

 

[ddub25]

No, B11 will always be either empty or positive. Thanks.

Dan

 

[taurean]

Try the following
=IF(OR(B11=0,B11=""),"q",IF(SUMIFS(N5:N16,Q5:Q16,"")=0,"h","The Else Case"))
Replace "The Else Case" with something you need which will come in picture when B11 is positive and SUMIFS is greater than 0.

 

[Rick Rothstein]

Is this what you are looking for?

=IF(B11=0,"q",IF(SUMIFS(N5:N16,Q5:Q16,"")=0,"h",SUMIFS(N5:N16,Q5:Q16,"")))

 

[vds1]

Try This,

=IF(AND(B11>0,SUMIFS(N5:N16,Q5:Q16,"")=0),"h",IF(OR(B11=0,B11=""),"q","Condition Variance"))

 

[ddub25]

Thanks for both of your help. Solved my problem. Thanks.

Dan

 

[ddub25]

=IF(C13=0,"q",IF(SUMIFS(M5:M17,O5:O17,"")=0,"h",SUMIFS(M5:M17,O5:O17,"")))

Here is the most recent update of the formula. I'd like to add an extra condition to the formula returning 'q'.

At present it returns 'q':
IF C13 =0

I would like the following:
IF C13 =0
AND IF M5:M17 =0
Then return "q"

Any ideas. Thanks.

Dan

 

[Rick Rothstein]

At present it returns 'q':
IF C13 =0

I would like the following:
IF C13 =0
AND IF M5:M17 =0
Then return "q"

You say AND, but I think you really mean OR...

=IF(OR(C13=0,M5:M17 =0),"q",IF(SUMIFS(M5:M17,O5:O17,"")=0,"h",SUMIFS(M5:M17,O5:O17,"")))

If I'm wrong, then just change the OR to AND in the above formula.

 

[ddub25]

Thanks very much Rick, yes OR was what I wanted. Thanks again.

Dan