Excel IF – How to Write an IF Formula when Every of N Conditions Have to Be Met

This is the classical concurrent condition: if all is true then proceed. You do it with an Excel IF function combined with AND function.

Imagine the following example: you will give a 6% bonus to each salesman who reaches three conditions (see graphic 1):

  • Product dealer is HP
  • Sales amount greater than 10,000
  • Contract year is 2005

Well, let’s start building the formula…

The IF function syntax is this one: IF(logical_test,value_if_true,value_if_false)

In brief, the “logical_test” argument is based on three conditions (AND retrieves TRUE if each one is true).

In your own words, the formula would be:IF(n conditions are met with AND, do this, if not do this other thing).

Graphic 1

In brief; the formula could be something like this (see results in column E, graphic 1):

=IF(AND(C2=”HP”,B2>10000,B2<100000,YEAR(D2)=2005),0.06,”Not applicable”)

Important notice

You can build this logical formula by nesting IF functions in each “value_if_true” argument but this is the hardest way. See example below:

=IF(C2=”HP”,IF(B2>10000,IF(B2<100000,IF(YEAR(D2)=2005,6%,”Not applicable”),”Not applicable”),”Not applicable”),”Not applicable”)