The three most commonly used formulas in Excel for doing simple math calculations are COUNT, SUM, and AVERAGE. Whether you’re managing your financial budget in Excel, or just keeping an eye on your next vacation, you’ve probably used one of these functions before.
In this article, we’ll go over the basics of these three functions and their respective and useful counterparts: COUNTIFS, SUMIFS, and AVERAGE.
Let’s say we are starting a new online mobile phone business and we have a spreadsheet that lists the sales we made in the first two months. Download a sample Excel spreadsheet here .
Excel COUNT, SUM, and AVERAGE
To find out how many mobile phones we have sold we can quickly use the COUNT formula as below:
= COUNT (E2: E16)
Alternatively, to get the total amount of sales we have made, we can use the SUM formula as shown below:
= SUM (E2: E16)
Finally, to find out the average sales we made for all phones, we can use the AVERAGE formula as shown below:
= AVERAGE (E2: E16)
The result should be like this:
The COUNT, SUM, and AVERAGE formulas will only work for records where the cell value is in numeric format. Any entry in the formula range (for example, E2: E16 in this example) that is not numeric will be ignored.
So make sure that all cells in the COUNT, SUM, and AVERAGE formula are formatted as numbers and not as text. Try using the same formula, but with a range of E: E instead of E2: E16. It will return the same result as before, because it ignores the title (i.e. Sales Price), which is in text format.
But what if we want to know the number of sales, total sales and average sales per phone only for those sold in the US? COUNTIFS, SUMIFS and AVERAGEIF play an important role here. Pay attention to the formula below:
COUNTIFS
Formula breakdown:
- = COUNTIFS (- “=” denotes the beginning of a formula in a cell, and COUNTIFS is the first part of the Excel function we use.
- D2: D16 – refers to the data range to check if it meets the criteria for inclusion in the counting formula.
- “USA” – search criteria in the specified data range (D2: D16)
The formula returns 6, the number of sales of items shipped from the US warehouse.
SUMIFS
Formula breakdown:
- = SUMIF (- “=†again indicates the beginning of the formula.
- E2: E16 – Refers to the range of data that we would like total, i.e. i.e. the sales price in our example.
- D2: D16 – refers to the data range to check if it meets the criteria for inclusion in the total.
- “USA” – search criteria in the specified data range (D2: D16)
The formula shows the total sales of items shipped from the US warehouse to $ 6,050.
AVERAGEIFS
Formula breakdown:
- = AVERAGEIFS (- “=” indicates the beginning of the formula.
- E2: E16 – Refers to the range of data that we would like to average. In this In this example, we want to get the average sales for all phones sold in the US.
- D2: D16 – refers to a data range to test if it meets the criteria that should be included in the average formula.
- US is the criteria for searching in the specified data range.
The formula shows that in the US we sold this product for about $ 1008 per phone.
All three formulas can accept more than one criterion. For example, if we want to find out the same numbers (i.e., COUNT, SUM and AVERAGE) for products sold in the US, but specifically for the Samsung brand only, we just need to add a data range to validate and then its criteria.
See the example below where the second criterion is added to the initial criteria checks. (Blue text indicates the first criterion, and red indicates the second criterion)
= COUNTIFS ( D2: D16, “USA” , < span style = "color: # ff0000;"> B2: B16, “Samsung” E2: E16, D2: D16, “USA” , B2: B16, “Samsung” E2: E16, D2: D16 , “USA” , B2: B16, “Samsung”
You will notice that Excel also has the formulas COUNTIF, SUMIF, and AVERAGE without the “S” suffix. They are used in the same way as COUNTIFS, SUMIFS, and AVERAGEIF. However, those who do not have the “S†suffix in their formula can only use one criterion for each formula.
Since the syntax is slightly different, I would recommend using COUNTIFS, SUMIFS, and AVERAGEIFS only, since it can be used for one or multiple criteria if needed. Enjoy!
–