SUM not working in certain cells only

[gers1978]

Ok, I have a table with 10 rows. Each row is pulled through from the bottom row of another workbook. I have 10 such workbooks, and this table pulls through the bottom row from each to make a sort of "summary" table.

It used INDEX MATCH to get the data, and pulls it through just fine. In all 11 workbooks (10 + summary) the tables are in A4:Z11.

I have a totals row in my summary table, and I noticed that in column R, the total is 0. All other column's total row is correct.

R4:R9 are indeed 0, which is correct, but R10 is pulling through £12,531 and R11 £112,137, again both correct. So surely the total row for R should be £124,668?

Now what's weird is when I select R10 and R11, the little SUM you get at the bottom status bar of Excel says 0. The COUNT is correct, saying 2, but SUM says 0. If I copy and paste values on R10 and R11, that little SUM in status bar still says 0.

However if I manually type 12531 and 112137 into R10 and R11, the SUM at the bottom (and the totals row of the table) add up correctly.

What's even weirder is if I select Q10 and Q11 or P10 and P11 (which all have non zero numbers in), the SUM at the bottom status still says 0 (again, the COUNT is correct) but in these columns the totals row is adding just fine anyway!

The formula for every cell in my summary table is:

=INDEX(path\[Workbook.xlsx]Sheet1!$A$4:$Z$11,MATCH($A5,'path\[Workbook.xlsx]Sheet1!$A$4:$A$11,0),MATCH(R$4,path\[Workbook.xlsx]Sheet1!$A$4:$Z$4,0))

(obviously the lookup reference changes in each cell)

Also posted here: http://www.ozgrid.com/forum/showthread.php?t=199601&p=769955#post769955

 

[mole999]

Now what's weird is when I select R10 and R11, the little SUM you get at the bottom status bar of Excel says 0. The COUNT is correct, saying 2, but SUM says 0. If I copy and paste values on R10 and R11, that little SUM in status bar still says 0.

would indicate that these are being treated as text, either a leading space or some other issue, hence they don't add

 

[gers1978]

Now what's weird is when I select R10 and R11, the little SUM you get at the bottom status bar of Excel says 0. The COUNT is correct, saying 2, but SUM says 0. If I copy and paste values on R10 and R11, that little SUM in status bar still says 0.

would indicate that these are being treated as text, either a leading space or some other issue, hence they don't add

Hmm, on the source sheet and the summary sheet they're both set to Currency but you may be onto something

 

[mole999]

that's a certainty, couple of methods for dealing with it, remove space, format as number, you can use a double minus in B1 for instance =--A1 which forces it, or multiply by 1 just can't remember how to apply it to your formula

=INDEX(path\[Workbook.xlsx]Sheet1!$A$4:$Z$11,MATCH($A5,'path\[Workbook.xlsx]Sheet1!$A$4:$A$11,0),MATCH(R$4,path\[Workbook.xlsx]Sheet1!$A$4:$Z$4,0))

to make that happen for you

 

[gers1978]

that's a certainty, couple of methods for dealing with it, remove space, format as number, you can use a double minus in B1 for instance =--A1 which forces it, or multiply by 1 just can't remember how to apply it to your formula

=INDEX(path\[Workbook.xlsx]Sheet1!$A$4:$Z$11,MATCH($A5,'path\[Workbook.xlsx]Sheet1!$A$4:$A$11,0),MATCH(R$4,path\[Workbook.xlsx]Sheet1!$A$4:$Z$4,0))

to make that happen for you

I've stuck a *1 at the end, and low and behold it works!

What's weird is I checked the source sheet and for the totals row that I'm pulling through, each cell has:

=SUMIF(TableName[[#Headers],[#Data],[Column1]:[Column1]],"<>*Summary",TableName[[#Headers],[#Data],[Column2]])

and this all works fine.

For the columns from my original post that are causing problems, someone has overwrote the formula above in the source sheet with an actual, typed-in value. I don't know this would cause the issue, as all cells are formatted as Currency anyway...

 

[RoryA]

Do you see the currency symbol in the formula bar when looking at one of the cells that was manually typed in? If so, the entry will be text, not a number.

 

[gers1978]

Do you see the currency symbol in the formula bar when looking at one of the cells that was manually typed in? If so, the entry will be text, not a number.

Yep, currency symbol is there. Also if I right-click the cell and choose "Format Cells", it's set as Currency

 

[RoryA]

Yes, but if you set it to another format, the display won't change because it's text. A number entered into a currency formatted cell will not show the currency symbol in the formula bar, only in the cell.

 

[gers1978]

Yes, but if you set it to another format, the display won't change because it's text. A number entered into a currency formatted cell will not show the currency symbol in the formula bar, only in the cell.

Ok, I'm not sure what you're telling me here though.

 

[RoryA]

I'm saying that if you select a cell and see, for example, £1,213.45 in the formula bar, it's text and SUM will ignore it. If it displays as £1,213.45 in the cell but shows 1213.45 in the formula bar, then it's a real number and SUM will include it.