Forum Discussion
How to sum dollar amounts
Hello,
How do I sum only the dollar amounts in a column that also contain text and other number values, such as percentages?
Example column as follows: (my report has many columns and I would like to obtain the total dollar amount for each column)
| 5/26 - 6/8 |
| wk 3 & 4 |
| 14.2% |
| $32,947.43 |
| 100341 |
| 5-25-18 |
| wk 1 & 2 |
| 22.5% |
| $5,860.35 |
| 100351 |
| 5-31-18 |
| wk 4 & 5 |
| 22.5% |
| $31,843.24 |
| 100322 |
| 5-17-18 |
| last day 5-29 |
| 100% |
| $35,387.50 |
| 100300 |
| 4-30-18 |
| wk 3 & 4 |
| 100% |
| $40,915 |
| 100342 |
| 5-25-18 |
| wk 12 & 13 |
| 92.9% |
|
Hello,
as a human being, how do you identify the numbers you want to include in the sum? Is there a pattern? How would you describe the logic in words? You need to give Excel something logical, some rules to work out which number to sum. The $ sign in front of the number can possibly work.
The Cell() function can identify certain formats. If a number is formatted with an accounting or a currency format, the formula CELL("format",A1) will return the letter C, followed by the number of decimals of the format, i.e. C2 for a number formatted with 2 decimals. Other letters returned could be P for percentage or G for General.
You could try using a helper column with the Cell formula cited above, but extracting only the first character. =LEFT(CELL("format",B1),1)
Then run a Sumif() to sum only the cells in the values column where the helper column has a "C".
Note that if you change the format of a cell, you will need to recalculate the cell manually for the Cell function to update.
Does that help?
