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
Alternatively & validate with Ctrl+Shift+Enter (or equiv. on Mac) if not Excel 365/Web:
=SUM(rowB * IF(ISNUMBER(SEARCH({1\2\3\4\5}, C1)),1,0))
Lorenzo please look at the reply to SergeiBaklan below. I explained as well as attached a screenshot of the problem in question. Thanx