{"id":67488,"date":"2020-03-16T10:49:01","date_gmt":"2020-03-16T09:49:01","guid":{"rendered":"http:\/\/onsoftware.en.softonic.com\/?p=67488"},"modified":"2025-07-01T21:22:34","modified_gmt":"2025-07-02T04:22:34","slug":"10-excel-formulas-for-pros","status":"publish","type":"post","link":"https:\/\/cms-articles.softonic.io\/en\/10-excel-formulas-for-pros\/","title":{"rendered":"12 essential Excel formulas: become an Excel pro"},"content":{"rendered":"

Is Microsoft Excel too complicated? It doesn’t have to be. Excel uses formulas to pull in information to a single cell from others around it. These works like Excel shortcuts making life easier. If you know the right formulas, Excel doesn’t have to be complicated at all.<\/p>\n

Here are\u00a012 Excel formulas<\/strong>\u00a0that are easy to remember and easy to apply. Some are basic formulas but others are advanced Excel functions that may sound tricky, so the best thing to do is to follow each example as it comes and then apply it to Excel as you read through the article.<\/p>\n

\r\n
\r\n
\r\n
\r\n \"\r\n <\/div>\r\n
\r\n Microsoft Excel<\/span>\r\n Download now \u25ba<\/a>\r\n <\/div>\r\n
\r\n <\/path><\/path>7<\/text><\/svg>\r\n <\/div>\r\n <\/div>\r\n
\r\n <\/span>\r\n <\/div>\r\n
\r\n \r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n

1. The sum (SUM)<\/h3>\n

This allows you to add several figures, just like in school. There are two ways.<\/p>\n

The first allows you to add cells in sequence (columns or rows). Just put\u00a0=SUM(first cell:last cell)\u00a0<\/strong>in the cell where you want to display the result.<\/p>\n

Variant<\/em>: This formula also allows you to add up all the numbers in a particular data\u00a0set (multiple column numbers and multiple rows), taking the \u201cfirst cell\u201d as the top left, and the \u201clast cell\u201d as the bottom right.<\/p>\n

The second method allows you to add (unrelated) cells individually. Just put\u00a0=SUM(cell1;cell2;cell3;cell4)<\/strong>\u00a0in the cell where you want to display the result.<\/p>\n

Example<\/em>:<\/p>\n

\"ExcelTo add the contents of the column a eg. cell A1, enter\u00a0=SUM (A1:A5)<\/em>\u00a0in the cell of your choice.<\/p>\n

To add all the numbers in the pivot\u00a0table, enter\u00a0=SUM (A1:B5)<\/em>\u00a0in the cell of your choice.<\/p>\n

To add only the cells with a negative number like in cell b2, enter\u00a0=SUM (A1;B2;B5)\u00a0<\/em>in the cell of your choice.<\/p>\n

2. The “If Function” sum according to a specific criterion (SUM.IF)<\/h3>\n

A table can come in the form of a list of several items, which aren\u2019t necessarily related. If the list is long, it can be difficult to find all the identical elements to add them one by one. To do this, you can ask Excel to find all these specific cases and make the sum using the formula\u00a0=SUM.IF(range_cells;\u201dtest\u201d;sum_range)<\/strong>. The \u201ccriterion\u201d is the item type to be found in the table. The range_cells is the range of cells containing the relevant criterion. The sum_range is the range of cells where the numbers add up.<\/p>\n

Example<\/em>:<\/p>\n

\"IfIn this example, the criterion to look for is \u201cred\u201d. To calculate the total of the \u201creds\u201d only, enter the following formula:\u00a0=SUM.IF(B1:B5,red\u201d,A1:A5)\u00a0<\/em>in the cell of your choice.<\/p>\n

\r\n
\r\n
\r\n
\r\n \r\n <\/div>\r\n
\r\n

Best apps and online tools to stay focused and be more productive<\/p>\r\n READ NOW \u25ba<\/a>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n

3. The sum according to several criteria (SUM.IFS)<\/h3>\n

A table can contain a lot of information. Excel can add data based on multiple criteria using a special formula:\u00a0=SUMIFS(sum_range;range_criterion1;criterion1; range_criterion2;criterion2;\u2026.)<\/strong>. sum_range is the range of cells with the numbers to be added. range_criterion1 represents the range of cells containing the data with the first criterion, range_criterion2 being the second criterion, and so on. criterion1 is the first item type (criterion) to be found in the table, etc..<\/p>\n

Example<\/em>:<\/p>\n

\"Excel<\/p>\n

To find the number of points earned by the men on the yellow team, enter the following formula:\u00a0SUMIFS (D1:D13,C1:C13,C1,B1:B13,B1)\u00a0<\/em>in the cell of your choice.<\/p>\n

Variation<\/em>: You don\u2019t have to select the first and last cell from the column exactly, you can\u00a0select an entire column<\/strong>.\u00a0Instead of B1: B13, just enter B:B.<\/p>\n

4. The sum of events based on a criterion (NB.IF)<\/h3>\n

Within a range of statistics, it\u2019s sometimes interesting to know how many times a given event is repeated in a column. For this, we use the following function:\u00a0=NB.SI(range_cell;\u201dcriterion\u201d)<\/strong>. The range_cells is the range of cells containing the data. The criterion is the first criterion to be found in the table.<\/p>\n

Example<\/em>:<\/p>\n

In the preceding table, you want to know how many women were involved in the game. Enter the following formula:\u00a0=NB.SI (B1:B13, “Woman\u201d)\u00a0<\/em>in the cell of your choice.<\/p>\n

\r\n

More about Windows 10<\/p>\r\n

\r\n \r\n
\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

How to start Windows 10 in safe mode \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n \r\n

\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

How to turn on Bluetooth in Windows 10 \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n

\r\n \r\n
\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

Windows 10 has a new free Microsoft Office app \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n \r\n

\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

Microsoft\u2019s latest trick to get you to upgrade to Windows 10 \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n

5. The sum of events based on several conditions (SUMPRODUCT)<\/h3>\n

This formula is used to extract a statistical answer to the question:\u00a0how many times do these specific conditions occur in my table?<\/em><\/p>\n

Answer:\u00a0=SUMPRODUCT((range_criterion1=\u201dcriterion1\u2033)*(range_criterion2=\u201dcriterion2\u2033))<\/strong><\/p>\n

The range_criterion1 is the range of cells containing the data on the first criterion, the range_criterion2 corresponds to the second criterion, and so on. criterion1 is the first criterion to be found in the table, etc..<\/p>\n

Example<\/em>\u00a0:<\/p>\n

Taking the above table as an example, this formula allows us to say how many men were part of the yellow team. To do this, enter the following formula:\u00a0=SUMPRODUCT((B1:B13=\u201dMale\u201d)*(C1:C13=\u201dyellow\u201d))<\/em>\u00a0in the cell of your choice.<\/p>\n

6. The sum of events between two specific values (SUMPRODUCT)<\/h3>\n

SUMPRODUCT is a statistical tool because it focuses on the number of times that certain conditions are met simultaneously. It allows you to find the number of times the figures are between two specific values, using the following formula:\u00a0=SUMPRODUCT(range_cells>=minimum)*(range_cells<=maximum))<\/strong>.<\/p>\n

The range_cells is the range of cells containing the relevant figures. Minimum and Maximum are the two values between which our search results will appear.<\/p>\n

Example<\/em>:<\/p>\n

In our previous table, we must find the number of players who scored between 150 and 200 points. To do this, enter the following formula:\u00a0=SUMPRODUCT((D1:D13>=150)*D1:D13<=200))<\/em>\u00a0in the cell of your choice.<\/p>\n

7. Average (AVERAGE)<\/h3>\n

We probably don\u2019t need to give you the definition of average.\u00a0It\u2019s very simple to achieve with the following formula:\u00a0=AVERAGE(range_cells)<\/strong><\/p>\n

Example:<\/em><\/p>\n

\"Average<\/p>\n

To calculate the average of all these numbers \u2013\u00a0 in other words, the number that represents the typical value or mean of the figures, enter the following formula:\u00a0=AVERAGE(A1:A6)<\/em>\u00a0in the cell of your choice. The range_cells is the range of cells containing the relevant figures.<\/p>\n

8. Concatenate<\/h3>\n

The Concatenate formula is a simple string formula that will pull information from many different cells into one. A good example would be combining a person’s name, address, and date of birth into one column when previously the information had been spread across different cells.<\/p>\n

To find. Concatenate you need to hit\u00a0Formulas<\/strong>, then\u00a0Text and Formulas<\/strong>, and then hit the\u00a0Insert Function\u00a0<\/strong>button. From there hit\u00a0Category\u00a0<\/strong>and\u00a0Text<\/strong>. You’ll see it there as\u00a0CONCAT.<\/strong><\/p>\n

\r\n
\r\n
\r\n
\r\n \r\n <\/div>\r\n
\r\n

5 Chrome extensions that can make you more productive<\/p>\r\n Read now \u25ba<\/a>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n

9. The maximum and the minimum (MAX and MIN)<\/h3>\n

Where you have a wide range of data, it can be quite a task to look for the largest or smallest number.\u00a0Fortunately, Excel has a formula that looks for you.\u00a0It\u2019s very easy to apply since it appears as follows:<\/p>\n

=MAX(range_cells)<\/strong><\/p>\n

=MIN(range_cells)<\/strong><\/p>\n

The range_cells is the range of cells containing the relevant figures.<\/p>\n

Variation<\/em>: you can also apply the search to multiple cell ranges.<\/p>\n

The formulas then become:<\/p>\n

=MAX(range_cells1;range_cells2)<\/strong><\/p>\n

=MIN(range_cells1;range_cells2)<\/strong><\/p>\n

Example<\/em>\u00a0:<\/p>\n

In the preceding table, you\u2019re looking for the greatest numerical value. Enter the following formula:\u00a0=MAX(A1:A6)<\/em>\u00a0in the cell of your choice.<\/p>\n

If you want the smallest numerical value, enter:\u00a0=MIN A1:A6)<\/em><\/p>\n

As you might guess, this formula is particularly useful if you\u2019re working with very large tables.<\/p>\n

\"Excel<\/p>\n

10. Count up the number of cells with numbers (COUNT)<\/h3>\n

I don\u2019t know why, but I always think of The Count from Sesame Street whenever I use this formula.<\/p>\n

COUNT<\/strong>\u00a0is a simple formula in Excel that simply counts up the number of cells in a given range that have numbers in them. Whereas the\u00a0SUM<\/strong>\u00a0function returns the total value of all the numbers in the range,\u00a0COUNT<\/strong>\u00a0is binary \u2014 it delivers a \u201c1\u201d if the cell has numerical contents and a \u201c0\u201d if it doesn\u2019t.<\/p>\n

This particular formula only works with numerical values. It\u2019s cousin,\u00a0COUNTA<\/strong>, does the same for all alphanumeric values in the cell and is ideal for testing whether cells in a range are \u201cempty.\u201d<\/p>\n

Example Formula: =COUNT(A1:A20)<\/p>\n

\r\n
\r\n
\r\n
\r\n \r\n <\/div>\r\n
\r\n

Five fantastic free alternatives to Microsoft Office<\/p>\r\n Read now<\/a>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n

11. Get the Length of the Contents with LEN<\/h3>\n

Need to get a character count of the contents of a particular cell? Here comes LEN to the rescue! LEN counts up the total number of characters in a given cell. Note that this includes spaces!<\/p>\n

Example Formula: =LEN(A1)<\/p>\n

\"Excel<\/p>\n

12. Recognize dates (DATEVALUE)<\/h3>\n

Excel can recognize certain serial numbers in select cells as being dates. For example, today’s date or the current date, whatever they may be, will be saved as an Excel serial number. You can use the\u00a0=DATEVALUE(…)<\/strong>\u00a0in an empty cell\u00a0<\/strong>to change date figures to the Excel serial numbers and vice versa. This is useful for making date calculations, and sorting and filtering dates.<\/p>\n

In short<\/h3>\n

While they may seem quite tricky at first, these Excel\u00a0formulas will save you a lot of time if you use the software on a frequent basis, so it\u2019s worth becoming familiar with them.<\/p>\n

\r\n

More about Excel <\/p>\r\n

\r\n \r\n
\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

10 games for Microsoft Excel?! And free?! \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n \r\n

\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

What would Minecraft look like in Microsoft Excel? \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n

\r\n \r\n
\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

7 tricks to use Microsoft Office free, without paying a cent \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n \r\n

\r\n
\r\n
<\/div>\r\n <\/div>\r\n
\r\n

5 ways Microsoft Office is better than Google Drive \u25ba<\/p>\r\n <\/div>\r\n <\/div>\r\n <\/a>\r\n <\/div>\r\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Is Microsoft Excel too complicated? It doesn’t have to be. Excel uses formulas to pull in information to a single cell from others around it. These works like Excel shortcuts making life easier. If you know the right formulas, Excel doesn’t have to be complicated at all. Here are\u00a012 Excel formulas\u00a0that are easy to remember … Continue reading “12 essential Excel formulas: become an Excel pro”<\/span><\/a><\/p>\n","protected":false},"author":2040,"featured_media":67490,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","wpcf-pageviews":18},"categories":[2518,2441],"tags":[1060],"usertag":[],"vertical":[],"content-category":[],"class_list":["post-67488","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-guides","category-how-to","tag-microsoft-office"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts\/67488","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/users\/2040"}],"replies":[{"embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/comments?post=67488"}],"version-history":[{"count":1,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts\/67488\/revisions"}],"predecessor-version":[{"id":325331,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/posts\/67488\/revisions\/325331"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/media\/67490"}],"wp:attachment":[{"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/media?parent=67488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/categories?post=67488"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/tags?post=67488"},{"taxonomy":"usertag","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/usertag?post=67488"},{"taxonomy":"vertical","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/vertical?post=67488"},{"taxonomy":"content-category","embeddable":true,"href":"https:\/\/cms-articles.softonic.io\/en\/wp-json\/wp\/v2\/content-category?post=67488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}