1

this time I did find a solution for my problem in here already but I could not figure out how to use it.

So basically I want to convert strings like

"aa#b#ccc#1#2"

into a horizontal array like 

{aa.b.ccc.1.2}

to use them later on with Sumproduct. So this article seems to do the job:

Split a string (cell) in Excel without VBA (e.g. for array formula)

In there I found the formula:

TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99))

However this will return an vertical array like:

{aa;b;ccc;1;2}

When I combine this with Sumproduct like

Sumproduct((a5:a10)*(b5:b10=ABOVE FORMULA))

I only get the sum of "aa" but not the rest. I tried transpose(ABOVE FORMULA) but it did not do the job.

Can you help me out?

Many greetings, Peter

PS: another problem is that my numbers become strings but this is something I can handle

PPS: {aa.b.ccc.1.2} This type of array is what I see when I press F9 on the formula "{=b1:f1"}

Peter Frey
  • 361
  • 4
  • 17
  • 1
    Transpose will certainly transform it into a horizontal array. So the problem lies elsewhere. Add a table to your question showing the contents of `a5:b10` so we may reproduce your problem. – Ron Rosenfeld Feb 22 '18 at 17:26

1 Answers1

2

Use ISNUMBER(MATCH()) instead:

Sumproduct((a5:a10)*(ISNUMBER(MATCH(b5:b10&"",ABOVE FORMULA,0))))

Even with SUMPRODUCT, you need to use Ctrl-Shift-enter instead of Enter when exiting edit mode.

Edit:

A slightly shorter and less volatile version of your formula:

=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+1,99))

so in total:

=SUMPRODUCT((A5:A10)*(ISNUMBER(MATCH(B5:B10&"",TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+1,99)),0))))

And use Ctrl-Shift-enter instead of Enter when exiting edit mode.

enter image description here

As you can see, we are passing the vertical array to the MATCH:

enter image description here

Which results in the proper array of TRUE/FALSE to pass to SUMPRODUCT:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Hi Scott, well done and thank you. Also, thank you for the very detailed explanations. Now my excel sheet is filled with tons of formula and references already and I fear that my excel sheet will become too complicated and too slow if I use this method extensively to spare me some arrays. Can you have a look at https://stackoverflow.com/questions/48986075/excel-function-reference-to-array-in-cell and check if such a style can reference to arrays? – Peter Frey Feb 26 '18 at 10:19