SUM

The SUM formula provides the sum or addition of all numbers, ranges, or values provided to the function.

 The syntax of this formula is

EXCEL
= SUM (number1, [number2], …)

GOOGLE SHEETS
= SUM (value1, [value2, ...])

number1 or value 1: it is the mandatory argument that you need to provide

[number2] or [value2, ...], …: one or more optional arguments that can be provided

The arguments number1, [number2], ... can be either numbers, ranges, formulas, or their combination as shown below:

In the image below, cell B8 contains the sum of the numbers appearing in the range B2 to B7:

SUMPRODUCT

The SUMPRODUCT formula is useful when you need to multiply a set of ranges and then add them. An example of such a scenario is where you need to calculate a weighted average for instance.

The syntax of the formula is:

= SUMPRODUCT (array1, [array2], [array3], …)

array1: first range or array of numbers to multiply and then add

array2, …: optional argument, next range, or array of numbers to multiply and then add

An example of the SUMPRODUCT formula is shown below. In this example the units sold and the price per unit is known.

Using the SUMPRODUCT formula in cell G4, we can easily calculate the value of total sales, without having to calculate the sales value for each item by each salesperson.

You can also directly provide an array of numbers in the formula:

=SUMPRODUCT({1,2,3,4,5},{10,15,20,25,30})

RANK

The RANK formula is useful when we have a set of numeric data and we want to identify the order or sequence in which each value is compared to the other values.

Excel provides two formulas for calculating Rank. These are as follows:

Both have similar syntax and usage. The difference in how they rank the same values in a range. More on this shortly.

The syntax of the formula is:

EXCEL
= RANK.AVG (number, ref, [order])

= RANK.EQ (number, ref, [order])

GOOGLE SHEETS
= RANK.AVG (value, data, [is_ascending])

= RANK.EQ (value, data, [is_ascending])  

number or value: refers to the numeric value for which the rank needs to be calculated

ref or data: refers to the range or series of numeric values against which the number is compared for calculating its rank

[order] or [is_ascending]: optional value; refers to descending (0) or ascending (1) order. In case you do not provide a value then by default Excel and Google Sheets display the ranks in descending order

Difference between RANK.AVG and RANK.EQ

The difference between the two different RANK formulas is how they treat more than one value have the same rank.

If all the numeric values are different then RANK.AVG and RANK.EQ will provide the same result.

RANK.AVG Formula

As we can see in the images above, the units sold for April are the highest and when we rank the data in descending order, it gets Rank 1, followed by Rank 2 for February. May and June have distinct values; therefore they have been assigned Ranks 4 and 3.

In the image below, the values of Units Sold has been made same as 5,000. If we use, RANK.AVG to calculate the Rank, then it calculates the average of Rank 3 and 4 and displays 3.5 as the Rank.

RANK.AVG Formula - Provides average rank for same values

On the other hand,  if we calculate the Rank using RANK.EQ, then it takes the higher of Rank 3 and Rank 4 and displays Rank 3 for both May and June.

RANK.EQ Formula - Provides the higher rank for same values

MAX

The MAX formula is used to identify the largest numeric value from a range of numbers.

The syntax of the formula is:

EXCEL
= MAX (number1, [number2], …)

GOOGLE SHEETS
= MAX (value1, [value2,...])

number1 or value1: it is the mandatory argument that you need to provide

[number2] or [value2, …]: one or more optional arguments that can be provided 

The arguments number1, [number2], ... can be either numbers, ranges, formulas, or their combination as shown below:

In the image below cell D4 is displaying the largest value in the range A2:A7 using the MAX formula:

MIN

The MIN formula is used to identify the smallest numeric value from a range of numbers.

The syntax of the formula is:

EXCEL
= MIN (number1, [number2], …)

GOOGLE SHEETS
= MIN (value1, [value2,...])

number1 or value1: it is the mandatory argument that you need to provide

[number2], … or [value2, ...]: one or more optional arguments that can be provided 

The arguments number1, [number2], ... can be either numbers, ranges, formulas, or their combination as shown below:

In the image below cell D4 is displaying the smallest value in the range A2:A7 using the MIN formula:

UNIQUE

The UNIQUE formula can be used to find distinct values or distinct rows from a set of data.

The syntax of the formula is:

EXCEL
= UNIQUE (array, [by_col], [exactly_once])

GOOGLE SHEETS
= UNIQUE (range, [by_column], [exactly_once])

array or range: the range or array from where the unique values are to be returned

[by_col] or [by_column]: optional value; it has two options
TRUE: searches for the unique values in columns
FALSE: searches for the unique values in rows. Defaults to FALSE if no value is provided

[exactly_once]: optional value; it has two options
TRUE: searches for items that appear only once
FALSE: search for all distinct items, irrespective of the number of occurrences. Defaults to FALSE if no value is provided

Example:

In an organization, there are requests made by multiple people every day for office stationery. From the data given below can you identify the unique set of departments making these requests.

In this example, the formula has been written in one cell and it reflects in the rows below automatically. This is the dynamic array functionality at play.

The formulas in the cells below (F3 and F4) are not editable and any changes that need to be made can be made only in the cell where the formula was written (F2)

In case you want to find out distinct requests, then you can filter out entire rows using the formula indicated below. In the original table:

rows 2 and 3 are identical,
rows 7 and 8 are identical,
rows 10 and 14 are identical,
and rows 11 and 12 are identical.

FREQUENCY

This formula helps in creating a frequency distribution in Excel.

The FREQUENCY formula makes use of dynamic arrays. Microsoft has made the usage of dynamic arrays simpler with the Microsoft 365 version. You can write the formulas with a dynamic array like any other formula. Google Sheets implementation is also similar.

In the earlier versions, it was required to press CTRL + SHIFT + ENTER after writing the formula. However, this is not required anymore and you can just press the ENTER key alone to write formulas using the dynamic arrays.

The syntax of the formula is:

EXCEL
= FREQUENCY (data_array, bins_array)

GOOGLE SHEETS
= FREQUENCY (data, classes)

data_array or data: the data for which the frequency distribution will be created

[bins_array] or classes: the upper limit of each interval; the value in bins_array (upper limit) will be included if you are using a continuous distribution

Example:

Let us take a scenario where data has been collected for a website for several visitors in the last 20 days. Using the MIN and MAX formulas it is easy to find out that the minimum and maximum values are 152 and 784. Therefore, we can have intervals of 100-200, 200-300, 300-400... to form the frequency table.

Begin by writing the upper limits in contiguous cells below each other (C3 to C9). Write the formula against the first limit (D3) and the rest of the cells (D4 to D9) will be auto-populated.

In the above image, you will notice a blue outline around the frequency values indicating that they have been calculated as a dynamic array.

If you click on any of the auto-calculated frequency values, you will not be able to edit the formula. They will appear in grey color as can be seen in the image below.