Forum Discussion
Solved
How to calculate an array from different columns
I need to calculate the following and need the correct formula: Setup Each column in row A has a heading named: | 1 | 2 | 3 | 4 | 5 | Each column has a different sum value in Row B: | 5 | 15...
Assuming Headers are sequential numbers this one
=SUMPRODUCT(INDEX(B2:F2,1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))also shall work on 2019
This is how things look when I open your sheet. After each try, I have closed the sheet and opened the original received from you. I did not save after each try.
1. This is a screenshot after I changed the content of B4 to 1,4,5 then pressing CTRL+SHIFT+ENTER
2. This is the screenshot after changing the content of B4 to 1,4,5 then pressing only ENTER.
I have tried it on another laptop using 2016 Pro Plus and another using 2016 as well.
I am not sure if there are any settings that I need to change or that we are still doing it wrong on our side. it is my First time using Array and VBA.
Thanks in advance Sergei.