Konfyt
New Member
I have an issue with a formula that works on my friend's laptop (who created it) but not on mine or two other laptops. Using Excel 2016 and 2019 Pro.
This is the objective:
How do I Calculate a Series linked to other data
SAMPLE FILE ATTACHED
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 | 40 | 30 | 25 |
Action
I enter the following column headings into cell B4 as a array/series:
| 1,3,5 |
Outcome in Cell B5 is:
| 70 |
Formula used:
{=SUMPRODUCT(INDEX(B2:F2,1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))}
Objective
From the example above, I calculate the Total of the Value of column 1,3,5 which is 5+40+25
Answer in Cell B5 should then reflect the total of 70
What is the issue, is that the formula does not recalculate when I enter 1,4,5 or any other combination in Cell B4. It defaults to 5 as the answer.
I used CTRL+SHIFT+ENTER to enter after entering 1,4,5 yet nothing happens.
This is the objective:
How do I Calculate a Series linked to other data
SAMPLE FILE ATTACHED
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 | 40 | 30 | 25 |
Action
I enter the following column headings into cell B4 as a array/series:
| 1,3,5 |
Outcome in Cell B5 is:
| 70 |
Formula used:
{=SUMPRODUCT(INDEX(B2:F2,1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))}
Objective
From the example above, I calculate the Total of the Value of column 1,3,5 which is 5+40+25
Answer in Cell B5 should then reflect the total of 70
What is the issue, is that the formula does not recalculate when I enter 1,4,5 or any other combination in Cell B4. It defaults to 5 as the answer.
I used CTRL+SHIFT+ENTER to enter after entering 1,4,5 yet nothing happens.