Excel: the most useful functions

1. Function: Fixed conditions for conditional sums

With the function "SUMIFIF" you can set fixed conditions for conditional sums in Excel.

In our example, we use data in cells A1 to A10 and the corresponding values in cells B1 to B10 that are used for the condition.
The example serves as a task that all values of column A should be summed if the value of column B is greater than 10.
The formula would then look like this: "= SUMIF (B1: B10;"> 10 "; A1: A10)".
The value "B1: B10" defines the area to be evaluated; then our condition: "> 10" - that is: greater than 10. Here you must note that the conditions must be written in quotation marks.
Finally, "A1: A10" defines the area to be totaled.

The "COUNTIF" function is suitable if, for example, you want to count all colored cells.

In our example, cells D2 and D4 are colored red.
The formula would then look like this: "= COUNTIF (D1: D4, 1)". The "1" at the end forms the search criterion, in our example the color red.
You will then receive the respective result in the cell in which you enter the formula.
Of course, you can not only search for the same colors, but also for the same values, content and more.

You can use the "IF" function to check arguments and then make a decision.

We have the following example: The value 10000 is in cell C14.
We want to determine the discount in field C15 and use the formula "IF", which looks like this in our example.
"= IF (C14 <10000; 0; 3)" - this means that a 3 should be entered in cell C15 as soon as the value in C14 is at least 10000.
In our example, we give a 3 percent discount if the total is at least 10, 000.

With the function "TEXT" you can have a certain text output.

To come back to our previous example with the discount: This formula is great for outputting a predefined text.
In our example, we want to issue a discount that should be displayed as a text form.
You can easily represent this as follows: "=" On your order from "& TEXT (C14;" # '## 0.00 ") &" we grant a discount of "& C15 &"%. "". The values come from the example in the previous formula.
The argument "" # '## 0.00 "", which must be written in quotation marks, represents the formatting of the number.
According to our example, the output reads: "We grant a 3% discount on your order of 13354.55."

You can display a document path with the "CELL" function.

When things get hectic in the office, this formula is ideal for displaying the path of the document.
It is sufficient to enter the formula "= CELL (" file name ")".
As output you get the complete path of the file as well as the name of the current worksheet, for example "Table 1".
You can then copy the path so that you can paste it into Windows Explorer, for example.

This practical tip is based on Office 2013 under Windows 7. In the next practical tip, read the five most annoying functions in Excel.