Forum Discussion
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 | 40 | 30 | 25 |
Action
I enter the following column headings into cell C1 as a series:
| 1,3,5 |
Outcome in Cell C2 is:
| 70 |
Objective
From the example above, I need to calculate the Total of the Value of column 1,3,5, which is 5+40+25
Answer in Cell C2 should then reflect the total of 70
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
13 Replies
- Lorenzo please look at the reply to SergeiBaklan below. I explained as well as attached a screenshot of the problem in question. Thanx
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))
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
