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
Depends on which Excel you are. As variant
=SUM(INDEX(rowB,1,XMATCH(FILTERXML("<y><z>"&SUBSTITUTE(C1,",","</z><z>")&"</z></y>","//z"),rowA)))
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.