Must Know Functions in Excel

Let`s start with the basic math and statistical functions you must know in Excel (i.e. SUM, AVERAGE and COUNT). Later on we will cover their respective variations.

Must Know Functions: SUM, AVERAGE & COUNT 

In the example below, we have a table on the left side which shows the product type your company sold in a given period, a notation if that sale was made “online” or in a “store” and the price you achieved in the sale of the products A, B and C. On the table on the right side we need to demonstrate in column I the result expected for each required information defined in column F.

Must Know Function SUM (No Condition)

So, if your manager asks you to calculate the total sales of all products in this period, regardless if the sale took place online or in your store, you just need to use the function SUM in cell I3 as per steps below:

1. Press equals (“=”) in cell I3 and type the word sum. A list with all the functions which contain the word “SUM” will pop up in the screen.

2. Select this first option (“SUM”) by pressing TAB.

3. The argument of this function is basically the range D3 to D20 which contains the values we want to sum up.

4. Finally, you can then close brackets and press ENTER. You can confirm your total sales was $ 937 during this period.

Function AVERAGE (No Condition)

Let`s now calculate the average of prices achieved by your company during this time. Note this calculation should not be restricted by any condition, so we need a simple average calculation for all products and including all sale types. As there`s no conditions here, simply use the function AVERAGE which is another function you must know. Follow the steps below:

1. Insert the function “Average” in cell I4, using the same procedure we did for the function sum.

2. Choose again the argument D3 to D20.

3. Close brackets and press ENTER. The result $ 52.1 will be displayed in that cell.

Function COUNT (No Condition)

To calculate the total number of sales that took place during period (i.e. for all products and for any sale type), use the function COUNT, which is another function you must know. Follow the steps below:

1. Insert the function Count in cell I5.

2. Select the range D3:D20 as the argument for this function.

3. Close brackets and press ENTER. The result 18 will be displayed on the screen.

Must Know Functions: SUMIF, AVERAGEIF & COUNTIF

So far we haven`t imposed any type of restriction on the calculations we performed. In case you need the sum, average on total number of datapoints in a range but subject to a single condition, you should then use the respective adapted functions to perform your calculations. So let`s check other 3 functions you must know while working with 1 condition in your dataset.

Must Know Function SUMIF (Single Condition)

For example, let`s sum up the values of the sales for product B, regardless if the sale took place online or in a store. As there`s only one condition in this example, we can then use the function SUMIF as per steps below:

1. Select this cell I7, press equals and type the word “SUM”. A list of functions will pop up.

2. The function you need is in this second position of the list. Press TAB to select that function.

3. You now need to supply 3 arguments for this function.

4. “Range” is the range related to all products from column B (i.e. range B3:B20). Press comma to skip to the next argument.

5. “Criteria” is the condition you want to impose, which in this case is the product “B” from cell G7. So, select that cell and type comma to skip to the next argument.

6. “Sum Range” are the values you want to sum up so select the range D3:D20. Close brackets and press ENTER. The result $ 397 will be displayed in that cell. Make sure to practice it as this definitely a function you must know at work.

Function AVERAGEIF (Single Condition)

Let`s now calculate the average of prices of product C (i.e. an average calculation with a single condition) which can be done through the function AVERAGEIF where the arguments are very similar to the ones used in the function SUMIF above. Follow the steps below:

1. Insert the function AVERAGEIF in cell I8.

2. The argument “Range” is the range B3:B20. Press comma to skip to the next argument.

3. Next, we have the “Criteria” which is the single condition you need to impose (cell G8). Press comma.

4. For the argument “Average Range”, select the same range containing the values of your first table (i.e. range D3:D20). Close brackets and press ENTER. The result $ 71.3 will be displayed in your spreadsheet.

Function COUNTIF (Single Condition)

We can then calculate the total number of products labelled as “A” in our sample. As we need to count a dataset with a single condition, here you need to work with the function COUNTIF. As opposed to the previous 2 functions we inserted, this one requires only 2 arguments. Follow the steps below:

1. Insert the function COUNTIF in cell I9.

2. The argument “Range” is again the range B3:B20. Press comma.

3. “Criteria” is the single condition you need to impose (cell G9) which represents product “A”.

4. For the argument “Average Range”, select the same range containing the values of your first table (i.e. range D3:D20). Close brackets and press ENTER. The result 4 will be displayed in your sheet.

Must Know Functions: SUMIFS, AVERAGEIFS & COUNTIFS 

Working with Math and Statistical functions with a single condition can be really helpful. However, in practice Excel users may face more than one restriction in their calculations. Whenever you need to consider 2 or more conditions in your dataset, you then need to use the respective adapted versions of these must know functions.

Must Know Function SUMIFS (Multiple Conditions)

For example, in cell I11 we need to calculate the sum of values from sales of product “B” which took place in a “store”. As there are 2 conditions this time, you need to work with the function SUMIFS this time. Before carrying on, note that the order of the arguments changes slightly compared to the other functions which are dependent of a single condition only. Follow the steps below:

1. So, insert the function SUMIFS in cell I11.

2. For this type of function, you need to start with the argument “Sum Range” just so all the other arguments are related to the conditions you want to impose. So, select the range D3:D20 this time.

3. The next argument is “Criteria Range 1” which in this case is the range B3:B20 which contains all the product names available in column B.

4. Next, you have the argument “Criteria 1” which is the criteria itself you are interested. In this case, you want the sum of values of all products labelled as “B”, so select cell G11 which contains that parameter.

5. From this point onwards you basically need to repeat steps 3 and 4 to address the remaining conditions. In this case, the second and last condition is to sum up all the sales that took place in a store. So for the argument “Criteria Range 2”, select the range C3:C20.

6. At last, for the argument “Criteria 2”, select cell H11. You can then close brackets and press ENTER. The result 196 will be displayed in your screen. This is probably the most important of the must know functions covered in this tutorial. Make sure you master it!

Function AVERAGEIFS (Multiple Conditions)

Let`s now calculate the average of prices of products “C” which have been sold “Online”. Again, as there`s more than one condition which need to use the adapted average function which in this case is AVERAGEIFS. Follow the steps below:

1. Insert the function AVERAGEIFS in cell I12.

2. For the argument “Average Range”, select the range where the values you are after are located (i.e. D3:D20). From this point onwards, we can then start addressing the sequence of criteria range and the criteria itself we need from our dataset.

3. After that, select the range B3:B20 for the argument “Criteria Range 1”.

4. For the argument “Criteria 1”, select cell G12 which represents product “C”.

5. “Criteria Range 2” is the range of all possible sale types from column C (i.e. C3:C20)

6. The argument “Criteria 2” is the type of sale we need (i.e. “Online”) so cell H12 in this case.

7. Finally, you can then close brackets and press ENTER, and the result 65 will be displayed to you.

Function COUNTIFS (Multiple Conditions)

Finally, we now need to count data points based on 2 conditions (i.e. number of products “A” sold in a “Store”). Here, we then need to use the last function you must know (function COUNTIFS) as per steps below:

1. Insert the function COUNTIFS in cell I11. For functions “Count” we don`t need to worry about the range where the values are located so we can basically concentrate on the argument related to the conditions themselves.

2. For the argument “Criteria Range 1”, select the range B3:B20

3. For the argument “Criteria 1”, select cell G13

4. For the argument “Criteria Range 2”, select the range C3:C20

5. After that, select cell H13 for the argument “Criteria 2”.

6. Close brackets and press ENTER. The result 2 will be displayed in your screen as there were only 2 sales of product “A” which took place in the “Store”.