SUM, AVERAGE, COUNT and conditional totals

Introduction

Functions are ready-made formulas. Instead of writing =A2+A3+A4+... for a hundred cells, you write =SUM(A2:A101). Excel has hundreds of functions, but a handful do most of the everyday work: adding, averaging, counting, finding the smallest and largest, rounding, and the conditional versions that total or count only the rows you care about. This lesson covers those.

How it works

A function is written as its name, then its arguments in brackets, separated by commas: =ROUND(B2,2). As you type a name, Excel lists matching functions and shows the arguments it expects.

The everyday functions:

  • =SUM(B2:B10) adds the numbers in the range. AutoSum (Alt+= on Windows, Command+Shift+T on Mac) inserts a SUM of the numbers next to the active cell.
  • =AVERAGE(B2:B10) gives the mean. Empty cells and text are ignored, but zeros count, so a 0 typed for "no result yet" lowers the average.
  • =MIN(...) and =MAX(...) give the smallest and largest numbers.
  • =COUNT(...) counts cells holding numbers, =COUNTA(...) counts cells that are not empty, and =COUNTBLANK(...) counts empty cells.
  • =ROUND(B2,2) rounds to two decimal places, with halves rounded away from zero, so =ROUND(2.5,0) is 3. ROUNDUP and ROUNDDOWN always go up or down. Unlike a number format, ROUND changes the value later formulas use.

The conditional functions only include rows that meet criteria:

  • =COUNTIF(A2:A10,"North") counts cells in A2:A10 equal to North (not case-sensitive).
  • =SUMIF(A2:A10,"North",C2:C10) adds the values in C2:C10 on the rows where column A says North.
  • =AVERAGEIF(...) works the same way for averages.

Criteria can compare: ">100", "<>Closed", or refer to a cell by joining an operator to it, as in ">"&F1. Wildcards work in text criteria: "A*" matches anything starting with A, and "?" matches one character.

For several conditions, use the plural versions. In SUMIFS the range to add comes first, followed by pairs of range and criteria: =SUMIFS(C2:C10,A2:A10,"North",B2:B10,"Pens") adds column C where the region is North and the product is Pens. COUNTIFS and AVERAGEIFS follow the same pattern. All conditions must be true at once.

Example

A sales list:

ABC
1RegionProductSales
2NorthPens120
3SouthPens80
4NorthBags300
5NorthPens50

Line by line

  1. =SUM(C2:C5) adds every sale.
  2. =AVERAGE(C2:C5) gives the mean sale.
  3. =COUNTIF(A2:A5,"North") counts the North rows.
  4. =SUMIF(A2:A5,"North",C2:C5) adds the North sales.
  5. =SUMIFS(C2:C5,A2:A5,"North",B2:B5,"Pens") adds North sales of pens only.
  6. =COUNTIFS(C2:C5,">=100") counts sales of at least 100.

Expected result

The formulas return 550, 137.5, 3, 470, 170 and 2.

In the real world

Monthly reports are mostly conditional totals: sales per region, expenses per category, orders per status. SUMIFS and COUNTIFS answer such questions directly from a raw list, and they remain the workhorse even for people who also use PivotTables, because a formula updates instantly and can sit anywhere in a report.

Common mistakes

  • Putting the sum range last in SUMIFS, as it is in SUMIF; in SUMIFS it comes first.
  • Typing 0 for missing values, which pulls averages down.
  • Using COUNT to count text entries; COUNT counts only numbers, so use COUNTA.
  • Writing criteria like >F1 without joining: write ">"&F1.

Best practices

  • Keep criteria in cells and refer to them, so a report can be changed without editing formulas.
  • Use SUMIFS even for one condition: one pattern to remember.
  • Round only where the rounded value matters, such as money in invoices.

Keyboard shortcuts

ActionWindowsMac
AutoSumAlt+=Command+Shift+T
Insert a functionShift+F3Shift+F3
Accept the function Excel suggests while typingTabTab
Fill a formula downCtrl+DCommand+D
RecalculateF9F9

Summary

SUM, AVERAGE, MIN and MAX work on the numbers in a range, COUNT counts numbers and COUNTA counts non-empty cells. ROUND changes values for later calculations. SUMIF, COUNTIF and AVERAGEIF include only rows meeting a condition, and the plural SUMIFS, COUNTIFS and AVERAGEIFS take several conditions, with the range to add first in SUMIFS.

Quick check

1. Which function counts cells that are not empty, including text?
2. In SUMIFS, which argument comes first?
3. What does =ROUND(2.5,0) return?
4. How do you write a criteria meaning greater than the value in F1?

Key takeaways

  • SUM, AVERAGE, MIN and MAX ignore text and empty cells; COUNT counts numbers and COUNTA counts non-empty cells.
  • ROUND changes the value used in calculations, unlike number formatting.
  • SUMIFS and COUNTIFS total or count with several conditions; in SUMIFS the range to add comes first.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.