1

I was wondering if I can make a named range from a linestring in a single cell, like "1,2,3,6,7" or "1-3, 6-7", instead of having to type each value in its own cell.

In column C of sheet A, I want to sum the values of column B if the values of column A exists in my named range "B_range", otherwise it will be 0. I use the formula:

=if(count.if(B_range,A2)>0,sumproduct(sum.if(A:A,B_range;B:B)),0)

"B_range" consists of C2:E2 in Sheet B, but if possible, I would like to define "B_range" from the textstring values in B2 of sheet B.

enter image description here

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
Peter W
  • 11
  • 3
  • Short answer is "no" (in spite of @JvdV's excellent answer) because 2,3,4 (for example) is a list of values, not a named range. A named range could be something like A2,A3,A4 or A2,A3,A4:A5 - they have to be valid cell references. You would need VBA to create such a named range from a cell containing (say) 2,3,4 and add it so that it was recognised by the Name Manager. – Tom Sharpe Jan 14 '22 at 15:38

1 Answers1

1

This could be quite a tricky problem. A line like '2,3,4' not so much, but '1-3, 5,7` is allready much more problematic, let alone a combination of the two.

It is allready made easier if you'd have access to LET() and microsoft 365's dynamic arrays:

enter image description here

Formula in C2:

=LET(A,TRANSPOSE(MID(FILTERXML("<t><s>'"&SUBSTITUTE(E1,",","</s><s>'")&"</s></t>","//s"),2,LEN(E1))),B,--LEFT(A,FIND("-",A&"-")-1),C,IFERROR(--MID(A,FIND("-",A)+1,99),--A),D,MMULT((A2:A6>=B)*(A2:A6<=C),SEQUENCE(COUNTA(A),,,0)),IF(D,SUM(B2:B6*D),0))

Variables explained:

  • A - TRANSPOSE(MID(FILTERXML("<t><s>'"&SUBSTITUTE(E1,",","</s><s>'")&"</s></t>","//s"),2,LEN(E1))) will break up your initial string in an horizontal array. In our case {2,3,4,6-8}.
  • B - --LEFT(A,FIND("-",A&"-")-1) will retrieve the number on the left of the hyphen. Note that we concatenate variable 'A' with an hyphen for this purpose. The result: {2,3,4,6}.
  • C - IFERROR(--MID(A,FIND("-",A)+1,99),--A) will retrieve the number right of the hyphen. When no hyphen, the same as variable 'A' is returned: {2,3,4,8}.
  • D - MMULT((A2:A6>=B)*(A2:A6<=C),SEQUENCE(COUNTA(A),,,0)) will calculate if any of your 'Nr.'s are to be found inbetween the previous two variables 'B' and 'C'. An vertical array is returned: {0;1;1;1;0}.
  • IF(D,SUM(B2:B6*D),0) - Our final calculation is a simple IF() statement which will use D and spill the results down under 'Sum'.

I can get into much more detail, but the above should give you an idea of what happened here.

For those who find it interesting how to 'split' a string into an array, please follow this link.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you for a solution. A tricky problem with at tricky answer. Given my current skill level, and the desire to simplify my sheets and formulas, rather than to increase the complexity, I am going to read your answer as a "no" :) Thanks again. – Peter W Jan 18 '22 at 10:09