How to add numbers in Excel

Here are the main ways to add numbers in Excel: how to add two numbers in different cells, how to add a number to a whole range, and how to sum by condition. I know five main ways:

• arithmetic operators (for addition, the plus sign);
• functions;
• pivot tables;
• Paste Special;
• the !SEMTools procedure.

The addition operator (+)

The simplest and shortest way is the plus operator. Add the values in A1 and B1:

=A1+B1

Add a constant, for example 10, to the number in A1:

=A1+10

Add 10 to every cell of the range A1:B5 (Excel 365 and 2021, or as an array formula in older versions): the result spills into a range of the same size.

=A1:B5+10

SUM and other summing functions

• SUM is the basic function: it adds numbers, cells and ranges, for example =SUM(A1:A10) or =SUM(A1:A10,C1:C10). Text and empty cells are ignored.
• SUMIF adds the cells that meet one condition: =SUMIF(A2:A100,”East”,B2:B100).
• SUMIFS adds by several conditions at once.
• SUMPRODUCT multiplies arrays element by element and adds the results, for example price × quantity.

The quickest way to insert SUM is AutoSum: select the cell below a column of numbers and press Alt+=. And to just see the sum without any formula, select the cells and look at the status bar at the bottom of the window.

Pivot tables for summing

Pivot tables are a powerful tool for summing and other aggregation: they group large datasets, build summary reports and show sums, averages and other statistics.

• Select the data range: it should be a table with a header row.
• Go to Insert → PivotTable and choose where to place it.
• Drag the field to group by into Rows, and the numeric field into Values: Excel sums it by default.
• Add filters and columns to look at the data from different angles.

Pivot tables are useful for analyzing and summarizing large tables and seeing trends in them. For a quick summary without settings, see how to count unique values with a fast pivot.

Paste Special: add a number to a whole range

Paste Special adds a number to all selected numbers at once, right in place:

• type the number in any cell and copy it (Ctrl+C);
• select the range (or several ranges with Ctrl);
• right-click → Paste Special → Add → OK.

The catch: formulas in the range become formulas with “+10” appended, and the formatting of the copied cell may be pasted too if you don’t choose Values.

!SEMTools procedures

With my !SEMTools add-in, you add any number to a selected range in a couple of clicks. The data is updated in place, with no helper columns or functions.

Add a number to all values in a range

Select the range (hold Ctrl to select several cells or ranges), run the procedure from the Arithmetic operations menu, type the number and click OK.

Add a percentage to one or more numbers

To add N percent to a number, multiply it by (100 + N) percent: to add 20%, multiply by 120%. As a formula, it’s =A1*1.2. With !SEMTools, it’s one more procedure in the same menu, and you can also multiply numbers in place the same way.

Common mistakes

• SUM returns 0 or less than expected. Some numbers are stored as text. Convert them to real numbers.
• =A1+B1 returns #VALUE!. One of the cells contains text, even a space. SUM ignores text, + doesn’t: use =SUM(A1,B1).
• The total is off by a cent. The values are rounded only by format. See rounding in Excel.

Summary

I hope you found what you were looking for on this page. The plus sign and SUM cover most needs, SUMIF and SUMIFS add by conditions, pivot tables summarize large tables, and Paste Special or the !SEMTools procedure add a number to a whole range in place.