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))
With that you need to change the formula each time names of headers in C1 are changed.
Except if I missed it I haven't seen something in the OP problem description/requirements saying that
| 1 | 2 | 3 | 4 | 5 | would/could change
Assuming this would be the case and next week columns are | 5 | 2 | 3 | 4 | 1 | the formula can be ajusted as follow to cover both scenario
=SUM(rowB * IF(ISNUMBER(SEARCH(rowA, C1)),1,0))