Ctrl+shift+enter

**stuntmannen** · 03-06-2013, 06:40 AM

Hello!

I've been googling for hours and I've seen people with similiar problems that got their problems solved.
But none of the solutions works for me.

I have two workbooks. One of them contains a list of different orders, and the other one is supposed to search in the first one.

The first problem is that if a order number appears several times I want the result to show all the results.
I've tried this formula:

Please Login or Register  to view this content.

But receive the error "The formula you typed contains an error" and the whole formula gets selected.

The code I found had commas instead of semi-colons and is, from what I understand, supposed to be an array.

And this is where my other issue comes in.

I can't create an array with CTRL+SHIFT+ENTER.
I've tried several different combinations of different controls and shifts. And even tried the return button without any results.
I found someone who said it could be helped out with CTRL+/ but that didn't work.
Neither did it work with having the cursor in the cell while trying, or doing the same procedure in the formula bar instead of the cell.
Nothing happens at all.

I'm using Excel 2010 and have a Swedish keyboard.

I've added two files to help out explaining what I'm trying to do.

Please help!

**Fotis1991** · 03-06-2013, 07:03 AM

Hi

See if this Array formula can helps you.

=INDEX([Ordernumbers.xls]Sheet1!$E$2:$E$1000;SMALL(IF([Ordernumbers.xls]Sheet1!$D$2:$D$1000=$C$3;ROW([Ordernumbers.xls]Sheet1!$E$2:$E$1000)-1);ROW([Ordernumbers.xls]Sheet1!E1)))

Read about ARRAYS formulas.

http://www.cpearson.com/excel/ArrayFormulas.aspx

**etaf** · 03-06-2013, 07:41 AM

you need to add a bracket before the +1 and also need a column reference in lat ROW which should be ROWS
=INDEX([Ordernumbers.xls]Sheet1!$E$2:$E$30000; SMALL(IF($C$5=[Ordernumbers.xls]Sheet1!$D$2:$D$30000; ROW([Ordernumbers.xls]Sheet1!$D$2:$D$30000)-ROW([Ordernumbers.xls]Sheet1!$D$2+1); ROW(1:1)))

=INDEX([Ordernumbers.xls]Sheet1!$E$2:$E$30000; SMALL(IF($C$5=[Ordernumbers.xls]Sheet1!$D$2:$D$30000; ROW([Ordernumbers.xls]Sheet1!$D$2:$D$30000)-ROW([Ordernumbers.xls]Sheet1!$D$2)+1); ROWS(1:1)))
also $C$5 is not your entry - should be $C$3

then in sum put
=SUMIFS([Ordernumbers.xls]Sheet1!$H$1:$H$179,[Ordernumbers.xls]Sheet1!$D$1:$D$179,$C$3,[Ordernumbers.xls]Sheet1!$E$1:$E$179,C11)

see attached sheet

**Kevin UK** · 03-06-2013, 09:56 AM

Hi stuntmannen

As you are using Excel 2010, you can take advantage of the AGGREGATE function for a non array formulas.
In C10 & copy down.

Formula:

Please Login or Register  to view this content.

In D10 & copy down.

Formula:

Please Login or Register  to view this content.

Commit the formulas as just enter.

**stuntmannen** · 03-06-2013, 10:30 AM

Fortis and etaf: You're examples worked just fine over here. But it's impossible for me to edit them since nothing happens after pressing CTRL+SHIFT+ENTER. I looked through the link with the array info, but that didn't help me much to solve the problem.

Kevin UK: You're example worked after replacing the commas with semicolons.

Is there any pros or cons in using formulas instead of arrays? I guess I will have to find a solution to the CSE-problem since there isn't always easier way to do it with formulas instead of arrays.