Here’s a simple overview of using common functions and operators to add and subtract ranges.
Download the Practice Workbook
How to Add in Excel
Example 1 – Add Numbers in Excel
Method 1 – Add Numbers with an Operator
- You can connect a series of numbers directly with the addition operator (+).
=2+3+4+5
- Or, you can also use relevant cell references to add numbers using an operator.
=C5+C6+C7+C8
Method 2 – Add Numbers Using SUM Function
- To add numbers using the SUM function in the range C5:C9, apply the following formula:
=SUM(C5:C9)
Example 2 – Get a Quick Total in Excel
- To get the sum of a range with just one click, select the range and navigate to the bottom of the Excel window. Excel properties will show the sum of the selected range.
Example 3 – Using the AutoSum Feature
- Select the cell under the range.
- Go to the Home tab.
- Click the AutoSum feature under the Editing group.
This command will insert the sum function for the range.
Example 4 – Add Times in Excel
- Use the cell reference and connect them with the Addition operator (+).
=B5+C5
Example 5 – Add Days to a Date in Excel
You can add days to date using 3 different approaches:
- Using the addition operator
- Using the DATE function
- Using the TODAY function
Example 6 – Add a Percentage in Excel
- To add the percentage of a number to that number, apply the following formula:
=C5+C5*$D$5Use the absolute cell reference if the percentage value is in a separate cell.
How to Subtract in Excel
Example 1 – Subtract Columns in Excel
- If you want to subtract two specific columns, subtract the first cells from the relevant columns first.
=D5-C5- Drag the Fill Handle tool down to copy the formula to the other cells.
Example 2 – Subtract the Same Cell from a Column
- Lock the cell to subtract in the formula by pressing the F4 key and then apply the subtraction operator.
=C5-$F$5
Example 3 – Subtract Multiple Cells from the Same Cell
- If you want to subtract multiple cells from the same cell, put that cell in the first position in the formula and then keep using the subtraction operator consecutively to connect the cells to subtract.
=E5-C5-C6-C7-C8
Example 4 – Subtract Times in Excel
- Whether your time format is 24 hours or 12 hours, subtract them with the subtraction operator just like you subtract numbers.
=B5-C5
Example 5 – Subtract Dates in Excel
To subtract dates in Excel, you can follow 3 ways:
- Just subtract with the subtraction operator
- Using the DATEDIF function
- Using the TODAY function
Example 6 – Subtract a Percentage in Excel
- To subtract the percentage of a number from that number, apply the formula below.
=C5-C5*$D$5
Example 7 – Subtract a Matrix in Excel
- When you have two matrices (two sets of values), apply the following formula:
=(B5:D7)-(F5:H7)
This is an array formula. Press the Ctrl + Shift + Enter key to apply it (in versions other than Excel 365) and you will see curly brackets which indicate the array formula.
Example 8 – Subtract a Portion of Cell Value
Case 8.1 – Case-Sensitive
- To subtract text from a cell value, use a combination of TRIM and SUBSTITUTE functions.
=TRIM(SUBSTITUTE(B5,C5,""))💡 Formula Breakdown
SUBSTITUTE(B5, C5, “”) replaces the occurrences of the text in cell C5 with an empty string (“”) and returns => Dell
TRIM function removes any excess spaces from the result of the SUBSTITUTE function.
TRIM(SUBSTITUTE(B5,C5,””)) = Dell
Case 8.2 – Case-Insensitive
=TRIM(REPLACE(B5,SEARCH(C5,B5),LEN(C5),""))💡 Formula Breakdown
The SEARCH function is used to find the position of the text in C5 within the text in B5.
SEARCH(C5,B5) => 6
LEN(C5) returns the length of the text in C5, which represents the number of characters to be replaced=> 6
TRIM(REPLACE(B5,SEARCH(C5,B5),LEN(C5),””)) = Dell
Example 9 – Subtract Two Lists in Excel
=IF(COUNTIF($C:$C, $B5)=0, "Not Matched", "")💡 Formula Breakdown
The COUNTIF($C:$C, $B5) is used to count the number of cells within the range $C:$C (column C) that match the value in cell $B5.
=0: This part of the formula checks if the count from the COUNTIF function is equal to zero. If the count is zero, it means that there are no matching cells found in column C.
IF(COUNTIF($C:$C, $B5)=0, “Not Matched”, “”): The IF function evaluates the condition (COUNTIF($C:$C, $B5)=0) and returns one of two results based on the condition
How to Add and Subtract in One Formula in Excel
Example 1 – Add and Subtract Multiple Cells from One Cell
We want to add a range of multiple cells and then subtract the summation from a single cell.
- Perform addition and subtraction in a single formula.
=C12-SUM(C5:C10)
Example 2 – Add and Subtract in Multiple Columns
We have two ranges of values (C5:C10 and D5:D10). We want to add the values by column and then subtract the summation of those values.
- Apply the following formula:
=SUM(C5:C10)-SUM(D5:D10)Frequently Asked Questions
How can I add or subtract numbers in Excel while ignoring errors?
You can use the IFERROR function to ignore errors.
Can I add or subtract numbers in Excel with decimal precision?
Yes, you can add or subtract numbers in Excel with decimal precision.
Takeaways from the Article
- The addition operator (+) is used to combine values and find their sum, while the subtraction operator (–) is used to find the difference between values.
- Formulas like “=SUM()” and “=A1-B1” allow you to add and subtract numbers in Excel.
- You can perform addition and subtraction on multiple cells at once using the SUM function.
Excel Add and Subtract: Knowledge Hub
<< Go Back to How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
