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

 

[gers1978]

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.

Got it, thanks. Since the £ was in the formula bar, I assume it was seeing it as text, hence SUM wouldn't work.

Any idea why it was seeing it as text, given the cell was formatted as currency?

 

[mole999]

Got it, thanks. Since the £ was in the formula bar, I assume it was seeing it as text, hence SUM wouldn't work.

Any idea why it was seeing it as text, given the cell was formatted as currency?

because the manually applied £ is a TEXT item. if you look at other currency cells you will not see that in the formula bar, only on the formatted cell, as it doesn't exist you can do sums with it

 

[gers1978]

because the manually applied £ is a TEXT item. if you look at other currency cells you will not see that in the formula bar, only on the formatted cell, as it doesn't exist you can do sums with it

Ahh ok thanks. So how does the *1 fix it?

 

[mole999]

you force the text to be treated as a number, excel gives in in this instance, "otherwise why else would the user try and multiply a word" says excel