I know I know the solution to this but it has been 2 years since I even opened an Excel Spreadsheet...

Anyway, lets say I have a column on Sheet1 pulling in numbers from Sheet2. On Sheet1, I want to SUM the total value of the numbers that are being pulled over.. but I am just getting 0

Can someone point me in the right direction here? I REALLLLLY appreciate it!

Should be as simple as:

=SUM(A:A)

That's what I thought but for some reason, it's not. The cells that it is summing, is pulling in data from another sheet. I thought that it should add them up just the same, but it is just giving me a result of 0

EX:

cell A1 contains ='Sheet2'!B1 and produces a result of 2000
cell A2 contains ='Sheet2'!B2 and produces a result of 3000
cell A3 contains =SUM(A1:A2) but produces a result of 0

?

Actually, where the problem seems to be is where the data is originating. Let me try to explain...

I have a sheet (Raw Data) where I paste these emails that I get that have client info on. These emails automatically parse the info into cells that contain things like this:

ClientName: Joe Shmoe

DebtAmount: 20000

that is all contained into one cell. I then use another sheet (Form Data) to extract just the specific client data (Joe Shmoe & 20000) from the field names (ClientName & DebtAmount).

This is where the problem seems to be. When I pull the DebtAmount (20000) into yet another sheet where I tally it all up, I get a 0. If I substitute the original formula for a simple numerical value, it works fine. But with this formula to separate the data from the field name, I get a 0. This is the formula I am using to extract the actual data from the field names:

=MID('Raw Data'!A216,FIND("=",SUBSTITUTE('Raw Data'!A216,": ","=",LEN('Raw Data'!A216)-LEN(SUBSTITUTE('Raw Data'!A216,": ",""))-1))+2,256)

That takes this:

CurrentBalance20: 20000

and gives me just the 20000 into a field. That is ultimately what I am trying to add together.

Does that make sense?

Well I figured out how I had gotten around this before...

I named the cells that were pulling in the data (creditor_1_debt, creditor_2_debt, etc) then just added them all together (=creditor_1_debt+creditor_2_debt)

This seems to do what I needed it to do.

Thanks

Quote:

---

Originally Posted by DrJ

Thats what I thought but for some reason, it's not. The cells that it is summing, is pulling in data from another sheet. i thought that it should add them up just the same, but it is just giving me a result of 0

EX:

cell A1 contains ='Sheet2'!B1 and produces a result of 2000
cell A2 contains ='Sheet2'!B2 and produces a result of 3000
cell A3 contains =SUM(A1:A2) but produces a result of 0

?

---

Sorry for the delay in answering. The values being displayed in A1 and A2 might "appear" to be numbers, but apparently are text strings. Try adding a +0 to the end of the formula to convert any numbers that are really text back into numbers.

The functions MID, LEFT and RIGHT are text functions, they return text. The +0 trick should fix the results back to normal numbers.