Forum Discussion
How can I add dozens of cells of data when each cell starts with $?
I receive an Excel spreadsheet with dozens of rows of dollar figures, but the data in each cell says $XX.xx. The autosum feature on Excel doesn't work, I think because of the "$" sign in each cell. How can I quickly sum those cells? Can I run a script to delete the "$" at the start of each cell's data? Is there another way to get autosum to work?
What happens if you
- Select the cells.
- Press Ctrl+H to activate the Replace dialog.
- Enter $ in the 'Find what' box and leave the 'Replace with' box empty.
- Click 'Replace All'.
4 Replies
If you want a formula that will accept dollar amounts as text (and have excel 365) you could use
= "$" & SUM( VALUE( TEXTAFTER(amount, "$") ) )The formula converts the text to numbers, calculates the total, and uses a "$" to reconvert the total back to text. More precise control over the output format could be achieved using TEXT and a number format "$#,##0.00".
The difference between absolute, relative and mixed references
Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.
Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.
Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.
The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three.
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
What happens if you
- Select the cells.
- Press Ctrl+H to activate the Replace dialog.
- Enter $ in the 'Find what' box and leave the 'Replace with' box empty.
- Click 'Replace All'.
- That works perfectly. Thanks!
