Forum Discussion

rafferoonie's avatar
rafferoonie
Copper Contributor
Sep 15, 2020

Problem adding up numerical results from index calculations

I wish to add up the numerical results of a series of index calculations. The index calculation result is showing on the sheet, but when I try and add up the column by typing in sum(b5:b42) it comes up blank. What am I doing wrong?

Sum will work when I have a column of figures produced using countif, but not the figures produced by index.

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 15, 2020

    rafferoonie 

    You may use double dash to convert texts to numbers

    =SUM(--B5:B42)

    but better to adjust initial formula to return numbers instead of texts.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 15, 2020
    Demo formula in German:
    INDEX('tabelle1'!$B$77:$M$77;;C1)+INDEX('tabelle1'!$B$70:$M$70;;C1)

    Demo Formula in englisch:
    INDEX('tabelle1'!$B$77:$M$77,,C1)+INDEX('tabelle1'!$B$70:$M$70,,C1)

    Think this will solve your problem.

    I would be happy to know if I could help.

    I wish you a long life with health, joy and love.

    Nikolino
    I know I don't know anything (Socrates)
  • himanshu_nassa's avatar
    himanshu_nassa
    Copper Contributor
    Sep 15, 2020

    rafferoonie 

     

    Looks like your data is getting converted to text and therefore does not result into a numerical summation. Try using Value function around Index to convert the data into numerical and then use sum as you are doing.

     

    =VALUE(INDEX(your index array and match))

Resources