Which Formula Totals Cells B2 to B6? SUM vs COUNT vs AVERAGE vs MAX

By the XLsheetAI Team · Updated September 27, 2026 · 8 min read

The short answer

=SUM(B2:B6) is correct. SUM means "add these up," and that is exactly what a total is.

The other three options look similar because they are all written the same way — a function name, a bracket, a range of cells — but each one does something completely different with those same five cells. Below is what each one gives you, so the distinction sticks rather than having to be memorised.

All four functions, same five numbers

Suppose cells B2 to B6 contain these values: 12, 7, 25, 7 and 4. Type each formula into an empty cell and here is what comes back.

Notice that the AVERAGE result, 11, looks like a perfectly reasonable number. That is the real reason this question gets asked: a wrong formula here does not produce an error message or a red warning. It produces a believable number that happens to answer a question you did not ask. Nothing in the spreadsheet will tell you.

What the colon actually means

This is the part most explanations skip, and it is worth thirty seconds because it unlocks every other formula you will ever write.

The colon means through. So B2:B6 means "B2 through B6" — all five cells, including both ends.

A comma means and. So =SUM(B2,B6) adds only B2 and B6, which with our numbers gives 16 rather than 55. It skips the three cells in the middle because you never mentioned them.

You can mix them. =SUM(B2:B6, D2:D6) means "everything from B2 through B6, and also everything from D2 through D6." Excel will happily add cells from anywhere in the workbook this way, as long as each block is separated by a comma.

That last row works, and for five cells it is barely slower to type. It is worth knowing why SUM is still the better habit: if you later insert a new row in the middle of the list, SUM(B2:B6) stretches to include it automatically, while the plus-sign version silently leaves it out. The longhand version does not break, which is exactly what makes it dangerous — it just quietly stops being right.

The fast way: never type it at all

Two shortcuts, both worth building into your habits.

AutoSum. Click the empty cell directly below your column of numbers, then press Alt and = together on Windows, or Cmd + Shift + T on a Mac. Excel writes =SUM(B2:B6) for you, having worked out the range from the numbers sitting above. Press Enter to accept it. Glance at the range it guessed before you do, because it stops at the first blank cell it meets.

The status bar. If you only want to see the total and do not need it in a cell, select the five cells and look at the strip along the very bottom of the Excel window. It already shows Sum, Average and Count for whatever you have selected. No formula, nothing added to your sheet. Right-click that strip to add Min, Max and others to what it displays.

Why SUM sometimes returns 0

You typed the formula correctly and got zero. Four causes, in the order they actually happen.

The text-not-number one accounts for most cases, and it is especially common in data pasted from a website, a PDF, or an export from another system. Numbers that arrive with a stray space, a currency symbol typed by hand, or a comma in an unexpected place all count as text as far as Excel is concerned. If you are wrestling with this regularly, cleaning messy data covers how to fix a whole column at once.

Two traps in the other three functions

Once you start using COUNT and AVERAGE for real, these two catch nearly everyone.

COUNT skips text, COUNTA does not

COUNT counts cells containing numbers. If two of your five cells contain the word N/A typed in by hand, COUNT returns 3, not 5. When you want "how many cells have anything at all in them," the function is COUNTA — the A stands for all. And when you want "how many are empty," it is COUNTBLANK.

AVERAGE treats blank and zero completely differently

An empty cell is skipped entirely — it does not add to the total and it does not count toward how many items you are dividing by. A cell holding the number 0 is a real value, so it counts as an item and drags the average down.

Say four of five students scored 10, 10, 10 and 10, and the fifth did not sit the test. If that fifth cell is left blank, AVERAGE returns 10. If somebody types a 0 in it, AVERAGE returns 8. Both numbers look fine. Only one of them answers the question that was asked. Whenever an average looks surprising, this is the first thing to check.

What to reach for next

Once plain SUM is comfortable, these three cover most of what everyday spreadsheet work needs.

That last one is a genuinely useful surprise. SUM adds every cell in the range whether you can see it or not, so filtering a list does not change the total underneath it. SUBTOTAL with a first argument of 109 adds only what is visible, which is almost always what you meant when you filtered.

Not sure which function you need?

Describe what you want in plain English — "total the sales but only for London" — and XLsheetAI writes the formula, explains what each part does, and gives you practice questions so it sticks. Free on iPhone and Android.

Ready for more? The Excel formulas cheat sheet covers the thirty-odd functions worth knowing, IF formula examples is the natural next step once totals make sense, and how to create a pivot table shows how to get dozens of these totals at once without writing a single formula.