Microsoft Excel_Aggregate Functions

Aggregate Functions such as SUM(), AVERAGE(), MIN(), MAX(), COUNT() & COUNTA() can be executed over a set of data (values) in Excel. These functions return appropriate result as the names of the functions might suggest you. 

Let us Discuss about these functions in detail:

SUM()

The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three.

There are a few types you can use to call sum function.

• =SUM(A2:A10) Adds the values in cells A2:10.
• =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.

For e.g.,

You can see the formula in the image above which adds all the values within the range D2:D4

Note:

If you use a formula like:

• =A1+B1+C1 or =A1+A2+A3

Your formula can break if there are any non-numeric (text) values in the referenced cells, which will return a #VALUE! error.

 SUM will ignore text values and give you the sum of just the numeric values.

Also, you can pass individual cells inside the sum function which is equally error prone as the ones having relative reference(=A1+B4) by using the below syntax.

SUM(number1,[number2],…)

The sum function can take upto 255 numbers in this way.

AVERAGE()

This function helps to find the average of a group of numbers or a given range.

For e.g.,

In the E1 cell we have given the formula as =AVERAGE(A1:D1), which returns the average from the data range A1,B1,C1,D1.

MIN()

MIN() function is used to find and return the minimum value within a data set.

For e.g. 

Notice that the minimum among 10, 9, 6, 11 i.e. 6 is returned.

You don’t have to manually find the result. Just type in the function and you’re good to go.

MAX()

Similar to MIN(), but MAX() returns the greatest value within the data set or a range of numbers.

For e.g.,

Going with the previous set of numbers, when we call MAX(), we get 11 as the result as it is the greatest number within the given range.

COUNT()

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20).

In this example, if four of the cells in the range contain numbers, the result is 4.

Note: COUNT function only counts cells which are populated with numbers. Any other data type will be ignored when counting the instances of numbers.

COUNTA()

The COUNTA function counts cells containing any type of information, including error values and empty text (“”). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.

In the given example the difference between COUNT() & COUNTA() is shown.

When we use COUNT() in the above range, we see that it ignores the text and the logical error.

But, when we use COUNTA(), it counts both of them the text as well as the error.

Sources

1. https://support.office.com/en-us/article/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509#:~:text=The%20COUNTA%20function%20counts%20cells,does%20not%20count%20empty%20cells.
2. https://support.office.com/en-us/article/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c#:~:text=Use%20the%20COUNT%20function%20to,numbers%2C%20the%20result%20is%205.
3. https://support.office.com/en-us/article/calculate-the-average-of-a-group-of-numbers-e158ef61-421c-4839-8290-34d7b1e68283
4. https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89?ui=en-us&rs=en-us&ad=us
5. https://support.microsoft.com/en-us/office/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df?ui=en-us&rs=en-us&ad=us