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"}

Improve this question
1
  • 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. Commented Feb 22, 2018 at 17:26

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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 stackoverflow.com/questions/48986075/… and check if such a style can reference to arrays?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.