How to calculate an array from different columns

SergeiBaklan
Diamond Contributor
Jul 24, 2021
Konfyt

Please try as array formula with Ctrl+Shift+Enter, I have no 2019 near me right now to test
- Konfyt
  Copper Contributor
  Jul 25, 2021
  SergeiBaklan
  Hi there,
  I have asked the question on another forum (chandoo.org) regarding the problem with CTRL+SHIFT+ENTER issue we encountered and received this change which solved that issue. I thought I would share it with you as you have really helped me a lot:
  
  =SUMPRODUCT(INDEX(B2:F2,1,N(IF(1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))))
  
  Here is yours for quick reference. It seems the only difference is the N(IF addition.
  
  =SUMPRODUCT(INDEX(B2:F2,1,FILTERXML("<y><z>"&SUBSTITUTE(B4,",","</z><z>")&"</z></y>","//z")))
  
  Thank You again SergeiBaklan
- Konfyt
  Copper Contributor
  Jul 25, 2021
  SergeiBaklan
  This is how things look when I open your sheet. After each try, I have closed the sheet and opened the original received from you. I did not save after each try.
  
  1. This is a screenshot after I changed the content of B4 to 1,4,5 then pressing CTRL+SHIFT+ENTER
  
  2. This is the screenshot after changing the content of B4 to 1,4,5 then pressing only ENTER.
  
  I have tried it on another laptop using 2016 Pro Plus and another using 2016 as well.
  
  I am not sure if there are any settings that I need to change or that we are still doing it wrong on our side. it is my First time using Array and VBA.
  
  Thanks in advance Sergei.

Forum Discussion

How to calculate an array from different columns

Resources