Forum Discussion
How to calculate an array from different columns
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
SergeiBaklan I have incorrectly stated C1 and C2. her is a screenshot of what I mean. Sorry for that. Late night.
I am using Excel 2019
B4 is the criteria, so I enter into B4> 1,3,5 then the answer must be the sum total of those cells B5> 70
NB: The entry in B4 changes. I might require 1,2,5 or other combinations.
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
- Thank you so far.
If I enter 1,4,5 in B4, it does not calculate the new query.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