You filter an invoice list by customer and the total at the bottom still shows the sum of everything, not what you can see. You show it to a colleague and both of you doubt the figure.
How to use Sum Formula step by step
1. Pick the kind of sum
A continuous range, single cells, the sum of products of two ranges, or only the rows still visible after a filter.
2. Enter the range or the cells
For single cells separate them with commas. With SUMPRODUCT the two ranges must be the same size.
3. Copy and paste
Paste the formula into the total cell. With SUBTOTAL the result changes as you apply or clear filters.
The Step Count: Traditional Friction vs. KorexTools
Adding by hand and comparing with the status bar (4 steps)
- Type SUM over the column.
- Filter and notice the total does not move.
- Search forums for the function that respects filters.
- Try SUBTOTAL codes until one fits.
With the KorexTools “Sum Formula” builder (3 steps)
- Pick "visible rows only".
- Enter the range.
- Copy the SUBTOTAL, already built.
Adding in Excel looks trivial until filters, numbers disguised as text, or two columns to multiply show up
SUM ignores text and adds numbers, including those in rows hidden by a filter, which surprises anyone who filters and expects a partial total. For the total to follow the filters you use SUBTOTAL with code 109. And when quantity must be multiplied by price before adding, SUMPRODUCT does it without a helper column.
The tool writes each variant with your ranges. For single cells it respects the references you type, for SUMPRODUCT it asks for two ranges, and for SUBTOTAL it adds code 109, which skips hidden rows. All with the separator and function names of your Excel install.
Which sum to choose in each case
- •One continuous column: SUM over the range, the same thing the AutoSum button does.
- •Quantity times price, line by line: SUMPRODUCT of the two columns, without creating a third.
- •A filtered list whose total should change with the filter: SUBTOTAL with code 109.
Four sums, four behaviours
| Formula | Counts hidden rows | Typical use |
|---|---|---|
| =SUM(A2:A20) | Yes | Total of a column |
| =SUMPRODUCT(A2:A20,B2:B20) | Yes | Quantity times price |
| =SUBTOTAL(109,A2:A20) | No | Total of a filtered list |
Common Mistakes and How to Avoid Them
- ⚠️Dragging a sum down a column that already contains the total cell.
- ⚠️Including the header row in the range.
Frequently Asked Questions
Why does my SUM return zero when I can see numbers?
Those numbers are probably stored as text. SUM skips them. Convert them with VALUE or by using Data, Text to Columns.
What does the 109 inside SUBTOTAL do?
It is the code for a sum that skips hidden rows. The 9 also adds, but it includes rows you hid by hand.
Is SUMPRODUCT good for more than multiplying and adding?
Yes, it takes conditions inside, so it can stand in for SUMIFS when you need to tell case apart or apply complex criteria.
How do I add up an entire column in Excel?
Choose “A range of cells” and type the whole column, such as B:B. The sum keeps working when you add new rows, but keep the formula itself out of that column to avoid a circular reference.
How do I add cells that are not next to each other?
Choose “Separate cells” and split them with commas, for example B2, D5, F9. The formula adds them in whatever order you like, with no need to select them while holding Control.
How do I add up hours that go past 24?
The sum formula is the same, but the result cell must have the [h]:mm format; with plain h:mm the total resets every 24 hours. The hours-worked tool in this silo explains it with examples.
How do I add only when a condition is met?
SUM does not filter on its own. To add only what meets a criterion use the SUMIF tool in this silo, which handles one or two conditions and writes the formula with your preferred separator.
What I would do if I were in your shoes
«When a total disagrees with your calculator, check the range first: the function rarely fails, but a range with one row too many is very common.» — DataKorex Engineering Team.