Aggregation is defined as several things grouped together or considered as a whole. Some day to day examples of aggregation are summing up the total on a restaurant bill by person, or calculating the average cost to fill up your tank of gas.

In this article we will cover the most common ways to aggregate data. However this is by no means an exhaustive list. Once you have the basic concepts from here just search for what it is you want to do, chances are high Excel already has this function or there is a convenient plug-in that lets you do the same.

SUM()

This is the one of the most common functions for aggregation. It does exactly what you would expect. a sum accepts a series of numbers and sums them together. It could be actual numbers, reference to cells or a group of cells, or a result of a function.

sum_1

All three methods above do exactly the same thing, In the first example we are putting the number as a direct input into the function.

In the second example we are entering distinct references to cells. Each reference must be separated from each other a comma or alternatively you can click on distinct cells while holding the Ctrlbutton.

Excel handily colours the cells when you edit the formula:

sum_2

 

In the third example we are specifying a range of cells – specifically from A5 to G5. It will sum up all the numerical values in the range but ignore non numbers (such as the + sign). Note that this doesn’t have to be a range, if we specified SUM(A1:G5) the result would be 16.

Remember you can mix and match anything in the sum parameters as long as they are numbers. Here is a needlessly complicated example to do the same as the examples above:

sum_3

COUNT()

Similar to SUM(), COUNT() will count all the integers rather than sum them. For example:

count_1

Just like SUM it will take any number and count the number of entries. Note however that like SUM(), COUNT() will only count numbers so if we need to count text it wont work. But there is another function we can use, more on this below.

 AVERAGE()

Similarly to COUNT() and SUM() this function gives you the arithmetic average of the numerical values in the parameters. So using AVERAGE on the example above would give us 2.5:

average_1

For those who need it, AVERAGE is the mathematical mean, if you need the mode or the median there is corresponding functions to that

Quick Aggregation

Sometimes you don’t need to store the aggregate number for later use, you just need a quick reference. Luckily in Excel you get the result of the three functions displayed whenever you select any cells. The summary is found at the bottom right of the window:

aggeregation_1

Note that for the purpose of the example I shrank the window significantly in reality it will be much further to the right of the screen

You can select any range of cells and it will do the three functions automatically. This is very handy when just needing a quick comparison or pulling checking your work.

Extended COUNT() and SUM() family

Besides just the regular version of SUM() and COUNT() there is additional functions to do more specific tasks

COUNTA()

As mentioned above COUNT() only counts numbers and ignores text. But what if I wanted to count text. For example the number of people on a list. Of course one way would be to put a 1 in a column adjacent to each name and then count that column. However there is a simpler way is the COUNTA() function, or as I call it the count all function. It works exactly the same as COUNT() but will count any cell that is not empty (even 0):

counta_1

All three formulas are aggregating from column A-G (i.e ‘=’ is excluded). While COUNT() only picks up the 4 zeros, COUNTA() also counts the plus signs giving you 7 total

COUNTIF()

Now it’s gonna get a little tricky. Let’s say you’re a teacher and you give out points to students that do well. And you want to know how many time Johnny Smith got points. You keep meticulous list of the points you gave out with a date, a name and number of points as each entry:

countif_1

So remember the task is to count how many times Johnny got points. Maybe for the first few months you can still count, but by the end of the year it will be too much. Here is where the COUNTIF comes in handy:

countif_2

COUNTIF works different that regular count, it take only 2 parameters. The first is the range to count. This could be a column or a row or any other selection. The second parameter is your criteria. In this case I put in to search for the text Johnny.

Alternatively we could use the text form another cell as the criteria:

countif_3

This is extremely handy if you are say doing a summary for each student, instead of typing out the formula each time you can just change the name in the adjacent cell to have a different result

For numbers you can specify not just a specific criteria but a range (for example >0):

countif_4

Note that for a range you need to use quotations around your criteria just like you would with text. Similarly “=0” is also valid instead of just using 0. Note that also in this example I am counting on column C instead of B, because B was the column for the names and I needed to count based on a specific name, while in this example I needed to count based on specific points which is what is in column C.

You can also reference a cell. However since we need to pass a string we need use the & operator to join the “>” together with the cell:

countif_5

So in this case we can easily reuse this formula for any value of points.

SUMIF()

Similar to COUNTIF(), SUMIF() is SUM()’s cousin which lets you sum based on criteria. You can use sum if just like we did with COUNTIF, so from the last example we can do the same with SUMIF():

sumif_1

So this works the same way as COUNTIF(), but notice something peculiar, in the hint below the formula there is a third parameter [sum_range]. Any parameter in square brackets is optional, but in this case it is probably almost always used. This lets you look at criteria in one column and sum another. When we looked at COUNTIF() we counted how many times Johnny got points, but the more obvious one would be how many points in total did Johnny earn:

sumif_2

So to break this down the SUMIF will look in the cells B2:B23 (which is the name column) for values equal to B25 which is “Johnny”. If we find it, then it will sum up together the contents of C2:C23 (which is the column for points).

SUMMARY

Today we learned 6 different functions allowing us to SUM() and AVERAGE()  and COUNT(), numbers or COUNTA() all. As well as start utilizing smarter logic to count and sum only specific items via COUNTIF() and SUMIF(). These concepts are key to understanding many advanced features of spreadsheets (Excel) and data based (Access). In fact Pivot tables are merely quick and clever ways for you to do these functions.

Hope you find this useful or at least you can walk away with a new word ‘Aggregation’. Leave your comments below and I’ll be looking forward for your feedback

Leave a Reply

Post Navigation