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
Hi there,
I have asked the question on another forum (chandoo.org) regarding the problem with CTRL+SHIFT+ENTER issue we encountered and received this change which solved that issue. I thought I would share it with you as you have really helped me a lot:
=SUMPRODUCT(INDEX(B2:F2,1,N(IF(1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))))
Here is yours for quick reference. It seems the only difference is the N(IF addition.
=SUMPRODUCT(INDEX(B2:F2,1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))
Thank You again SergeiBaklan