I created anew Excel worksheet by merging two sheets using "index ... Match" function. When I try to total one of the columns the result shows always zero. The files were converted from PDf . I tried changing formatting options but to no avail. file is atacched
try below
=SUM(--H2:H7)
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
What is your expected result?
The values in column H are text, not numbers. So they will be treated as 0. I seem to remember that India has some way of using commas in long numbers for currency that is unique. The string "10,00,000" is not meaningful as a number anywhere else that I know of.
Use Find and Replace to remove the commas from everything in column H and then apply the following custom format to that column:
Formula:
Please Login or Register to view this content.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Thanks. It works when I searched for "," and replaced it. Indian numeral system of thousands, lakhs, crores is perfectly current, allowed and one of the popular system and works well when you do direct data entry in cells. But here the data was imported thru PDF. In fact i repeatedly tried changing the format to currency , with and without 1000 separator but it wont accept.
Once i searched and replaced commas in imported file and then applied format throughout the column it worked.Thanks once again
Thanks . But i have used pivot tables to auot generate summary and it wont work. I have solved the issue thanks to the suggestion from "6StringJazzer"Originally Posted by samba_ravi
samba_ravi's formula will definitely do the arithmetic correctly, by forcing the values to be converted to numbers. However, my personal preference is to address data issues as early in the stream as possible, so in this case I recommended fixing the data itself. Glad that worked.
If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."
If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks