I have been trying to combine these formulas into 1 formula that can give me a true or false finding so I can then filter with it. I can't seem to get the syntax right as 1 formula, they work individually but I need them in 1 formula. Can anyone help?

=OR(AND($V7>81,$G7="CIAPPR",$P7="Purchase",$EX=""),$AC7="CRF")

=AND($V7>80,$G7="APPR",$P7="Purchase",$EX7="")

=AND($V7<81,$G7="CIAPPR",$P7="Purchase",$EX7="")

=AND($G7="APPR",$P7="OFFUS",$EX7="")

=AND($G7="APPR",$P7="ONUS",$EX7="")

To begin with, your first formula does not appear to be correct. Your $EX reference won't work because it refers to a column, not a cell. =OR(AND($V7>81,$G7="CIAPPR",$P7="Purchase",$EX=""),$AC7="CRF") ^^^^Second, it is not clear to me what you are trying to do. Are you trying to OR

allof theAND'stogether? i.e ifanyof the AND's are TRUEORthe $AC7="CRF" clause is TRUE, then return TRUE?If that's the case, then (after fixing the cell reference in the first formula) try putting all the other formulas before the closing parenthesis, separated by commas.

Something like this, which shows all the clauses you are checking for TRUE or FALSE. If

anyof those 6 clauses are TRUE, the entire formula will be TRUE.=OR(

AND($V7>81,$G7="CIAPPR",$P7="Purchase",$EX7=""),

$AC7="CRF",

AND($V7>80,$G7="APPR",$P7="Purchase",$EX7=""),

AND($V7<81,$G7="CIAPPR",$P7="Purchase",$EX7=""),

AND($G7="APPR",$P7="OFFUS",$EX7=""),

AND($G7="APPR",$P7="ONUS",$EX7="")

)If that works, I believe that the entire formula can then be shortened since you are checking for the same things multiple times. However, I don't want to go down that path until I'm sure that I understand what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks DerbyDad03 for the quick response, I feel like I owe you since you have helped me all year long. Thank you for all of your help, I wish you happy holidays :-) Yes the the formula was missing the row number (EX7); the formula you provided did work, I tweaked it a bit to add one more condition but the new condition doesn't seem to be working. I am using a wild card to eliminate anything with that contains the letters "Approval".

=OR(

AND($V34>81,$G34="CIAPPR",$P34="Purchase",$AH34<>"*Approval",$EX34="",$AC34="CRF"),

AND($V34>80,$G34="APPR",$P34="Purchase",$EX34=""),

AND($V34<81,$G34="CIAPPR",$P34="Purchase",$EX34=""),

AND($G34="APPR",$P34="OFFUS",$EX34=""),

AND($G34="APPR",$P34="ONUS",$EX34="")

)

Happy Holidays to you and yours also. You have discovered one of Excel's many quirks. Some functions accept wildcards within quotes, other functions consider a wildcard character within quotes to be a "literal" meaning that Excel is actually looking for the character, e.g. an asterisk.

The <> operator is not recognizing the asterisk as a wild card but as an asterisk.

The COUNTIF function

doesrecognize wildcards, so trying replacing this:$AH34<>"*Approval"

with this:

COUNTIF($AH34,"*Approval*")=0

or

NOT(COUNTIF($AH34,"*Approval*"))

Those formulas should both return TRUE when Approval is not found in the cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thank you, I actually need it to return false if those characters are there so I changed the 0 to a 1 in the count formula you provided but it is still returning true so something else is wrong. if those characters are in there regardless of any other test it should always come back false. stand alone this formula returns false

=COUNTIF($AH34,"*Approval*")=1but incorporated with the whole formula it still returns true:

=OR(

AND($V25>81,$G25="CIAPPR",$P25="Purchase",COUNTIF($AH25,"*Approval*")=1,$EX25="",$AC25="CRF"),

AND($V25>80,$G25="APPR",$P25="Purchase",$EX25=""),

AND($V25<81,$G25="CIAPPR",$P25="Purchase",$EX25=""),

AND($G25="APPR",$P25="OFFUS",$EX25=""),

AND($G25="APPR",$P25="ONUS",$EX25="")

)

You have a rather long and complex formula, have you

tried using the Evaluate Formula function from the ribbon?

See here:https://support.office.com/en-us/ar...

Stepping through the formula should give you an idea of where it is going wrong.

Also, with all the TEXT you are checking, make sure you do not have

any space characters at the end of the text your checking.The text string "APPR" is NOT the same as the text string "APPR " Space Character ^

MIKE

message edited by mmcconaghy

I'd like to ask one favor: Please be consistent with the cell references that you use. We don't have a copy of your workbook and therefore often have to set up a worksheet in order to test the formulas.

So far you have referenced Rows 7, 25 and 34. In fact, in your latest response you referenced different rows in your 2 examples. This just adds to the confusion as we try to understand what you are trying to do.

When you say =COUNTIF($AH

34,"*Approval*")=1 returns FALSE and then tell us that COUNTIF($AH25,"*Approval*")=1 returns TRUE when used incorporated in the formula, I don't know if you accidentally tested 2 different cells or what.If you could be consistent with your cell references then we can copy/paste your formulas into the test sheets and not have to keep modifying them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Let's take this piece by piece: You said:

"I actually need it to return false if those characters are there so I changed the 0 to a 1and

stand alone this formula returns false

=COUNTIF($AH34,"*Approval*")=1IF AH34 contains the characters

Approvalthat formula will return TRUE. As soon as COUNTIF sees those characters in a cell it will be set to "1" (TRUE) and since 1=1, the formula will return TRUE.If that standalone formula is returning FALSE for you, then you don't have Approval in AH34.

The COUNTIF must be tested against 0 for it to return FALSE when Approval is present in the cell.

Think about it: If Approval is in the cell, then the COUNTIF is TRUE. Testing True against 1 will return TRUE because an Excel TRUE = 1. If you test it against a 0 it will return FALSE if Approval is present because TRUE <> 0.

Set up this simple test:

G34 = CIAPPR

P34 = Purchase

V34 = 85

AC34 = CRF

AH34 = Approval

EX34 is blankThis formula, with the COUNTIF tested against 0, will return FALSE because everything is TRUE except for the COUNTIF() = 0 clause.

=AND($V34>81, $G34="CIAPPR", $P34="Purchase",

COUNTIF($AH34,"*Approval*")=0, $EX34="", $AC34="CRF")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03, I had the logic backwards and made a rookie mistake with the row numbers not matching up throughout the formula. I used the step through functionality recommended by mmcconaghy and figured out my mistake. I also had to add the countif formula to each AND statement, this may not be the most efficient formula but it works. Here is the working formula:

=OR(AND($V34>81,$G34="CIAPPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34="",$AC34="CRF"),AND($V34>80,$G34="APPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34=""),AND($V34<81,$G34="APPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34=""),AND($V34<81,$G34="CIAPPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34=""),AND($G34="APPR",$P34="OFFUS",$EX34=""),AND($G34="APPR",$P34="ONUS",$EX34=""))Thank you for your patience and thank you for the education :-)

message edited by mecerrato

Ask Your Question

Weekly Poll