Sep 15 2020 08:43 AM
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.
Sep 15 2020 08:49 AM
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))
Sep 15 2020 09:15 AM
Sep 15 2020 03:51 PM
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.