Does Microsoft Excel seem too complicated? In this article we present you 10 useful and interesting Excel formulas , easy to remember and ready to apply in your spreadsheets.

Microsoft Excel is the spreadsheet program par excellence. Praised for its wide variety of functions, it can be a bit challenging for all those who use it for the first time.

This labyrinthine program can perform almost any task and offers numerous options. To make matters worse, in Office this application has become even more powerful.

The simple sum (SUM)

It allows adding various numerical data, such as at school. There are two ways to do it.

The first allows you to add contiguous cells (in a row or in a column):

Type = SUM (first_cell: last_cell) in the cell where you want the result to appear.

Variant: This formula also allows you to add all the numbers contained in a specific rectangular area (formed by several rows and columns), if you consider as the first cell the one located in the upper left corner and as the last one that is in the lower vertex law.

In the following examples, "first_cell: last_cell" will always be designated with the term "range".

The second method will allow you to add individual (non-contiguous) cells:

Type = SUM (cell1; cell2; cell3; cell4) in the cell where you want the result to appear.

Example:

To add the content of the column on the left, write in a cell of your choice = SUM (A1: A5) .

To add the content of the entire table, write in a cell = SUM (A1: A5) .

To add only cells that have a negative number, write in a cell

= SUM (A1: B3: B5) .

Add according to a certain criterion (SUMAR.SI)

Note that this feature is only available as of Excel 2007 .

A table can be presented in the form of a list with different data, even if you do not want to work with all of them at the same time.

If this list is long, it can be tedious to select the identical items one by one to add them up. In this case, it is possible to order Excel to find them and then add them with the following formula:

= SUM.SI (range; "criterion"; range_sum) .

The "criterion" is the condition to look for in the table. The range is the range of cells that contain the criteria to evaluate. Sum_range is the range of cells where the numbers you want to add are found.

Example:

In this example, the criterion we are looking for is "red".

To just calculate the number of "reds", write the following formula in a cell: = SUMIF (B1: B5; "red"; A1: A5) .

Add according to several criteria (SUMAR.SI.CONJUNTO)

A table can provide a wide variety of information. Excel allows you to add numerical data that meet different criteria, thanks to this special formula:

= SUMAR.SI.CONJUNTO (suma_suma; range_criterios1; criteria1; range_criterios2; criteria2; ....) .

Sum_range is the range of cells where the numbers you want to add are found.

Criteria_range1 represents the range of cells that contain the data referred to the first criterion, criteria_range2 refers to the second criterion, etc.

Criterion1 is the first condition to look for in the table, etc.

To know the total points obtained by the members of the yellow team, write the following formula in a cell of your choice:

= SUM.CONJUNTO (D1: D13; C1: C13; C1; B1: B13; B1) .

Variant : It is not mandatory to select the first and second cells of a column or row, you can also select the entire column . Instead of B1: B13, it indicates only B: B.

Count the cells that meet a criterion (COUNTIF)

When it comes to statistics, you may want to know how many times information is repeated throughout a column. In this case, we use the following formula:

= COUNTIF (range; "criterion") .

The range represents the range of cells that contain the numerical data to analyze. Criterion is the condition to look for in the table.

Example :

In the previous table, we wanted to know how many women participated in the game. Then, we write the following formula in a cell: = COUNTIF (B1: B13; "Woman") .

Add the cells that meet various conditions (SUMPRODUCT)

This formula gives you an exact statistic to answer this question: How many times a series of specific conditions are fulfilled in your table . This formula will give you the answer:

= SUMAPRODUCT ((range_criteria1 = "criterion1") * (range_criteria2 = "criterion2")) .

Criteria_range1 represents the range of cells that contain the data referring to the first criterion, criteria_range2 refers to the second criterion, etc. Criterion1 is the first condition to look for in the table, etc.

Example :

If we use this function in the preceding table, we will be able to determine, for example, how many men are part of the yellow team. We only have to write in a cell the following formula:

= SUMPRODUCT ((B1: B13 = "Man") * (C1: C13 = "yellow")) .

Add the cells that verify two specific conditions (SUMAPRODUCT)
As we have already seen, SUMAPRODUCT is mainly a statistical tool capable of determining how many times certain conditions are met. It will allow you to know how many numbers are included between two specific values, thanks to the following formula: = SUMAPRODUCT ((range> = minimum) * (range <= maximum)) .

Range represents the range of cells that contain the numeric data to analyze. Minimum and maximum are the values ​​between which the numbers we are looking for should be.

Example :

Suppose that in the preceding table we want to know how many players scored between 150 and 200 points. Then, we must write in a cell the following formula:

= SUMPRODUCT ((D1: D13> = 150) * (D1: D13 <= 200)) .

The average (AVERAGE)

It would be a waste of time to repeat the definition of average. Mainly, because it is very simple to calculate it with the following formula: = AVERAGE (rank)

The range represents the range of cells that contain the numerical data to analyze.

Example:

Moyenne Excel

To calculate the average of all these numbers, write the following formula in a cell: = AVERAGE (A1: A5) . As in previous cases, it also works with a range of numbers in several columns or separated by commas.

Maximum and minimum (MAX and MIN)

Finding the largest or smallest value among a large amount of data can be a real nuisance. Happily, Excel provides a formula that does the work for you. It is very easy to use and is written as follows:

= MAX (range)

= MIN (range)

The range represents the range of cells that contain the numerical data to analyze.

Variant: You can apply the search to several cell ranges. In that case, the formulas would be:

= MAX (rank1; rank2)

= MIN (rank1, rank2)

Example :

If you want to know the highest value of the preceding table, you must write this formula in a cell: = MAX (A1: A6) .

If, on the other hand, you want to know the smallest numerical value, write: = MIN (A1: A6) .

Of course, these formulas are especially useful in large tables.

Bonus Track: How to calculate percentages in Excel

Before finishing we will make a brief review of the world of percentages in Excel. This section does not intend to delve deeply into the subject, but we invite you to leave any doubt about it in the comments. If we see enough interest we will consider making a specific tutorial.

If you want to obtain the percentage of a certain amount it is as easy as multiplying it by the percentage that we want to obtain.

Example:

Imagine that we want to find out what is 75% of 300, which is the value of cell A2. Simply enter the desired percentage in cell B2 while in cell C2 we introduce a formula that multiplies both values. The resulting value should leave 225. Here we leave you three multiplication formulas that you can use.

= 300 * 75%

= 300 * 75/100

= 300 * 0.75

To end

Although at first glance they may seem totally inscrutable, these Excel formulas will save you an incredible amount of time if you regularly use this program.