• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CTRL +SHIFT+ENTER not working

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.
 

Attachments

  • sum row by index.xlsx
    10.4 KB · Views: 4
@Konfyt,

Try this array (CSE) formula in Cell B5 :

=SUMPRODUCT(INDEX(B2:F2,1,N(IF(1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))))

Regards
 
@Konfyt,

Try this array (CSE) formula in Cell B5 :

=SUMPRODUCT(INDEX(B2:F2,1,N(IF(1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))))

Regards
If I have a scenario where every other column is empty and should not be seen as part of the SUM, how would I need to tweak this formula?

Kindly see the attached example
 

Attachments

  • sum row by index nth column.xlsx
    10.5 KB · Views: 7
For future reference?
In 365 it is possible to reverse the lookup and seek for the indices within comma-separated list and use the result to filter the second, value, row
Code:
= LET(sep,",",
  toFind, sep&index&sep,
  inString, sep&required&sep,
  isrequired?, ISNUMBER(FIND(toFind, inString)),
  SUM(FILTER(value, isrequired?)))

What would be better is to have a proper function to split comma-separated lists (the inverse of TEXTJOIN). The formula
Code:
= LET(
  item, --SPLIT.TEXT(required, ","),
  v, XLOOKUP(item, index, value),
  SUM(v) )
works fine on my machine, but that is only because I have Charles Williams's FastExcel addin installed.
 
Please try

=SUMPRODUCT(B2:J2,--ISNUMBER(FIND(","&B1:J1&",",","&B4&",")))
 

Attachments

  • sum row by index nth column.xlsx
    10.6 KB · Views: 1
Back
Top