One of the most powerful and widely used concepts of spreadsheet software is formulas. More specifically, formulas for aggregating data.
Let's build formulas by hand to match the results we saw via the Status Bar in the previous lesson.
SUM
The SUM (also commonly referred to as the "Total") is the adding-up of all numerical figures in a dataset. Below is the formula you'd use for Payment Amount in our Getting Started Payments dataset:
=SUM(E3:E27)
AVERAGE
The AVERAGE is the arithmetic mean of a dataset. Below is the formula you'd use for Payment Amount in our Getting Started Payments dataset:
=AVERAGE(E3:E27)
MIN
The smallest value in a dataset. Below is the formula you'd use for Payment Amount in our Getting Started Payments dataset:
=MIN(E3:E27)
MAX
The largest value in a dataset. Below is the formula you'd use for Payment Amount in our Getting Started Payments dataset:
=MAX(E3:E27)
COUNT
The number of numeric values in a dataset. Below is the formula you'd use for Payment Amount in our Getting Started Payments dataset:
=COUNT(E3:E27)
COUNTA
The number of values in a dataset. Below is the formula you'd use for Payment Amount in our Getting Started Payments dataset:
=COUNTA(E3:E27)
Here's a screenshot showing the custom build-out and display of these formulas (View → Show formulas):
And here's a screenshot showing the results:
A few additional versions of the COUNT formula that are commonly used:
COUNTIF
Enables you to COUNT only the values that meet specified criteria. For example, if you only wanted to COUNT the Payment Amount values >= $500:
=COUNTIF(E3:E27,">500")
COUNTUNIQUE
Enables you to COUNT only the number of unique vales in a dataset.
COUNTBLANK
Enables you to COUNT only the number of blank/empty values in a dataset.