How to use the DIFFERENT FROM sign in Excel?

What is the DIFFERENT FROM sign used for in Excel?¶

In its simplest form, the inequality sign determines whether the values ​​of the cells being compared are equal or not. According to the argument, the final truth value is TRUE Or FAKE, if the desired conditions are the same or different. The DIFFERENT FROM sign is rarely used separately in Excel, because only in combination with functions (e.g. IF, OR) that it shows its true usefulness. In the context of a function, it decides what should happen when a condition does not apply.

Excel: DIFFERENT FROM sign – syntax explained¶

The simplest form of application of the inequality sign is a function consisting of two conditions and the DIFFERENT FROM operator:

=(Condition1<>Condition2)

For example, we take as condition 1 Excel cell A1 with value 2 and as condition 2 cell B1 with value 3. Using the DIFFERENT FROM <> sign, we now ask if A1 is “different” from B1 and obtain as a result TRUE.

It is of course possible to perform this request to multiple cells, columns and values ​​at once. The syntax extends depending on the complexity of the conditions to be examined:

=(Condition1<>Condition2<>Condition3)

To analyze multiple active cells and columns in a project, simply drag the green box in the column that should display the desired output values ​​up to the last row of the relevant area. Then use the key [Entrée] to turn the function on absolute link.

In principle, the use of this sign is therefore quite simple. Things get complicated, however, when used in nested arguments and with large amounts of data. Our examples will help you see things more clearly.

Using the DIFFERENT FROM sign: examples¶

Now that the function of the inequality sign has been clarified, you will learn how to use this sign effectively in other functions by nesting. The values ​​obtained can help to refine an analysis, for example within a function IF, OR or NOT.

Example 1: sign DIFFERENT FROM + IF¶

It is especially the combination with the SI function which shows the advantages of the “Different from” sign. You will quickly notice that associated with the SI function, it hardly differs from the equal sign; it simply performs the exact opposite function. By combining it with the IF function, you request certain conditions and ensure that in a given case (IF), a given result (THEN) follows.

For example, the IF function could check who drew the winning number 104 in a lottery. In this example, the EQUAL and DIFFERENT FROM signs would be equivalent.

=SI(B3=104;"<Gagnant";"Non gagnant")

So the IF function says that IF the lot number in cell B3 is the same as the winning number 104, you get “Winner” as the output value. If not, you get the statement “Not Winning”. With the box to fill, you automatically apply the function again to all affected cells.

With the Excel sign DIFFERENT FROM, the function would be almost identical. Simply replace the EQUAL sign with the DIFFERENT FROM sign and change position of “WINNER” and “Non-Winner” in the formula:

=SI(B3<>104;"Gagnant";"Non gagnant")

Using the EQUAL or DIFFERENT FROM sign gives almost identical values, but the results are exactly opposite. Just make the use of operators dependent on the desired results.

Example 2: EQUAL sign + IF + OR¶

Excel gives you an almost infinite number of possibilities for examining complex, nested arguments in logical functions. For example, you can insert the EQUAL sign in a combination of IF and OR functions to obtain the desired output values.

In syntactic form, a possible function combining the sign DIFFERENT FROM, IF and OR is presented as follows in the example of a competition mentioned above:

=SI(OU(B3<>1;C3<>0;D3<>4);"Gagnant";"Non gagnant"))

In this example, three correct numbers must be matched to win. If the applicable condition occurs, “No Winner” must follow, otherwise “Winner”.

In the concrete example of the competition, this looks like this:

Example 3: DIFFERENT FROM sign + SUM IF function¶

The DIFFERENT FROM sign is also useful in combination with Excel's math function SUMIF. SUM.IF not only calculates the sum of values ​​in your table or column, but can also calculate the sum of very specific numbers in your calculation. In the following example, SUMIF should only add cell values ​​if neighboring cells are not emptythat is to say in this case the values ​​are NOT DIFFERENT FROM “” (empty).

=SOMME.SI(C3:C6;"<>"&"" ;B3:B6)

The function here must compare the values ​​of cells B3 to B6 and C3 to C6 and possibly add them. The colon is used to delimit the cells for which a sum is desired. Only cells that are not empty (““) or that are different should be included in the sum.

As you wanted, using the DIFFERENT FROM sign here allows you to obtain only the sum of the cells you have chosen.