# Report with IF, CONTAIN, AND and OR function

#### Ivan1984

##### New Member
Dear all,

I stuck to what I believe is a simple formula for you.
I think that if i contain and not contain is the solution for my problem, but I don't know how to set the formula.

An example is attached, the example column is what you would like to get.

I'm NOT very good at explaining, but I believe an example will be clear enough.

#### Attachments

• IF AND oR example.xlsx
8.9 KB · Views: 9
=IF(OR(AND(NOT(ISNUMBER(SEARCH("Q",B2,1))),C2="hybrid"), AND(B2="A6",C2="gas")),D2,IF(OR(AND(B2="a8",C2="diesel"),AND(ISNUMBER(SEARCH("A",B2,1)),C2="electro")),0,""))

expected results would have helped as a few do not meet the criteria

#### Attachments

• IF AND oR example-ETAF.xlsx
9.8 KB · Views: 12
Etaf thank you very much for quick answer.

I'm doing something for work so I changed the variable names and passed something in wrong. I'm sorry. The corrected query is attached. I have 2 columns that are my conditions and the amount is from the third. Tnx in advance.
I have another condition in column b but it doesn t matter right now.

#### Attachments

• Correct example.xlsx
8.9 KB · Views: 8
Last edited:
that will be quite a long nested IF
maybe its possible to put together the common results needed
for example - the end result is either value in C or 0

you sample file only show 1 zero in the TEXT info - but in the results you show 2 zeros......

thinking of , just using the zeros - and everything is C value - is that true
do you have all the possible values in A and B and results required

From you sample , I can just use 1 OR(AND( condition to show 0 and anythign else is C

=IF( OR(AND(A2="makegoods Sky", B2="Played"),AND(A2="makegoods HBO", B2="Played")), 0, C2 )

BUT i suspect we dont have all the possible combinations - otherwise we can provide a lot of OR(AND combinations - BUT again will need all possible entries

anyway - heres the example above

#### Attachments

• Correct example (1) - ETAF.xlsx
9.6 KB · Views: 11