SUM, COUNT, AVERAGE and their IFs - the basic Excel functions you can't live without (including a video tutorial)
Today I’ll be talking about some of the most basic yet important functions in Excel.
You actually can’t start using Excel without learning these functions first. It’s like when you first go to school and learn how to add and subtract.
These functions are SUM, COUNT, AVERAGE, SUMIF, COUNTIF, AVERAGEIF.
Simply you use SUM if you need to add any range of cells, COUNT to count a range of cells, and AVERAGE to get the average of a range of cells.
And you use their IFs if for example you need to calculate specific cells based on specific criteria.
Let’s see how to use those functions in reality.
We have an Excel table that contains City names, the population for each City and the country of each city.
And we would like to calculate these metrics. Some of them are straight forward, and some are based on specific criteria.
So for the first one: Sum of population in all cities.
The first thing we do is we go to the formula bar and put an = sign, sum and open bracket.
We then select the range that we would like to add and then close the bracket.
Another way instead of selecting the range exactly, is that we select the whole column.
This is useful so that when you update your table, the formula gets updated automatically. Otherwise you will have to go and update the range every time you add something to the table.
Next, is Count. We need to count all the cities. So again, we go to the formula bar, put an = sign, count and open bracket.
And then we choose the whole population column, and close bracket.
This is because count function only works with numbers and cities column is text. If we would like to count text, we should use COUNTA instead of COUNT.
But be aware, if you choose COUNTA with the whole column, the header will be included because it’s in text.
When we used COUNT with the population column which is all numeric, Excel counted only the numbers, and it automatically excluded the header in text.
Next we go to the ifs and we start with the SUMIF.
Here we would like to SUM the population of all the English cities only. Meaning that we need Excel to look which city is in England and then take it’s sum and exclude any other city.
So we go to the formula bar, = SUMIF, open bracket and the first argument here is the range that includes my criteria.
So we put the country column and then comma.
Now we put the criteria itself. And since our criteria is Cities in England, we type England, and notice that we put it between speech marks because it’s a text. If my criteria was a number we don’t need speech marks.
Then we put a comma, and put the sum range. And here we put the population because this is what we need to calculate “Sum of population in English cities”. Now we close bracket and hit Enter.
If I need to make sure my formula is correct, I will highlight the population for each English city and match the result in the status bar with the result in my cell.
Count works exactly the same way. We choose the range that includes my criteria, choose the criteria itself, and that’s it.
There is nothing called COUNT Range like the SUM range because Excel got the order to Count already.
In SUM, Excel wanted to know the SUM range: which cells do I want him to add.
The last thing we have is the Average.
So Average works the same as SUM and COUNT. Type the function and choose the range.
And AVERAGEIF works the same as SUMIF. Type the function, choose the range that includes the criteria, type the criteria and then choose the AVERAGE range.
Now you know BASIC Excel. Try these functions out and feel free to post any questions you have in the comments section below.