SUM not working in certain cells only

  • 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:


    Code
    =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.mrexcel.com/forum/e…lls-only.html#post4504307

  • Re: SUM not working in certain cells only


    Looks to me that your numbers are being read as text (type 2). You can add a 0 or multiply by 1 in order for it to force the text to be read as numbers and change it to type 1.

  • Re: SUM not working in certain cells only


    Quote

    R10 is pulling through £12,531 and R11 £112,137


    If that is exactly what shows in the formula bar (not just the cell), then that is text, not a number - excel does not include currency symbols in the formula bar for real numbers.


    If that is the case, then you need to track back and find out why the £ is being included in the cell

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!