Preview of Dynamic Arrays in Excel
Published Sep 25 2018 06:00 AM 162K Views
Microsoft

July 1st 2020 Update
Dynamic Arrays is now available to Office 365 users on all endpoints. 

 

Until now, you wrote a formula for each value you wanted returned to the grid. One formula, one value. If you wanted another value, you wrote (or copied) another formula. With dynamic arrays, that all changes. Now, you can write a formula hit the enter key and get an array of values returned. One formula, many values. This will allow you to build more capable spreadsheets, faster, with fewer formulas and less chance of error.
 
To harness the power of dynamic arrays, we’ve added some amazing new functions. So, for instance, you can use the SORT function to sort a list, the UNIQUE function to remove duplicates from that list, then use the FILTER function to get just what you want from the list. And when your data changes, the dynamic array will resize and recalculate automatically! 
 

Dynamic arrays in actionDynamic arrays in actionSpilling

We call the behavior of placing values in neighboring blank cells "spilling”, and you’ll see Excel indicate the formula’s “spill range" with a thin blue border when you select any cell inside the spill range.
 
Don’t worry about the spill range overlapping your data--if there isn’t enough space, the formula will roll up and show an informative #SPILL error. When you select the #SPILL error, the formulas desired spill range will be indicated by a dashed blue border. Just move or delete the obstructing data and your formula will automatically spill.
 
Spilling range not blankSpilling range not blank
Native to Excel
Dynamic array support is deeply integrated into Excel and it's not limited to the functions shipping alongside it -- any newly authored formula that returns an array will spill. For instance, entering =A3:A13 into B3 will cause the values in A3:A13 to be spilled into B3:B13. And just like Excel's grid, dynamic arrays can be 2 dimensional as shown in the multiplication table example below.
 
 Using dynamic arrays with existing functionsUsing dynamic arrays with existing functions
Referencing the spill range using A1# notation
Dynamic arrays may seamlessly resize as your data changes. To make it easy to reference resizing dynamic arrays, we are adding a way to reference the entire spill in a dependable, resilient way. You can do this by following a cell reference with the # symbol, for example A1#. This is equivalent to referencing the entire spilled range for the dynamic array in A1. We'll default to this style reference whenever you write a formula that refers to the entire spill range.
 
In the example below notice how the SUMIF function is using all the product names from the dynamic array in D5. When Grapes is added to the sales table, the D5 spill range grows and so does the result of the SUMIF because it references D5# rather than D5:D8.
 
 Using # to refer to the spill rangeUsing # to refer to the spill range
New Functions
Here is the full set of functions that will be accompanying dynamic arrays.
 
FILTER - filters an array of data based on criteria you define.
UNIQUE - returns a list of unique values from a list or range.
SORT - sorts an array of values.
SORTBY - sorts an array based on a corresponding array.
SEQUENCE - generates a list of sequential numbers, such as 1, 2, 3, 4.
RANDARRAY - returns an array of random numbers between 0 and 1.
 
We cannot wait to see how our users use these new building blocks in their spreadsheets.
 
Learn More
You can learn more about dynamic arrays from these resources:
 
 
Availability notes:
Dynamic arrays functionality is available in Preview for users signed up for the Office 365 Insiders Program starting today. We will initially roll out to a subset of Insider users on Windows so that we can gather feedback and monitor feature quality. Over the next few months, we'll be increasing the number of Insider users with access to dynamic arrays and light up support for Excel on Mac, web, and mobile. You’ll know if you have dynamic arrays if you see any of the new functions in your formula autocomplete when you start typing a formula.
 
To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.
 
Joe McDaid (@jjmcdaid)
Program Manager, Excel
266 Comments
Bronze Contributor

This is incredible. I cannot wait to try them out.

 

I am on Insider Fast in Office 365 but they don't exist for me yet. I think it is a shame that even if we sign up for Insider Fast the rollout of new features to try is spread out. I thought the point of Insider Fast was to get the latest publicly available features, not to sign up to be on a list to randomly get the latest features.

Iron Contributor

This is super exciting! I use the FILTER function in Google Sheets so having this functionality come to Excel is great.  I can already see several ways these new features are going to help out in my work, so I’m looking forward to trying them (and hoping to be in the initial Insider testing group to get them sooner!)

Copper Contributor

Thank you - this is a very exciting feature.

I have a few initial questions:

  • Can dynamic arrays be used with RTD-based functions? Currently there are some bugs (including buggy RTD topic disconnect) when RTD is called from legacy CSE array formulae (even if the RTD call is through an indirection). It would be an immense help if there were RTD support in conjunction with the dynamic arrays, for async, streaming and other scenarios.
  • Do I understand correctly that any UDF defined in a .xll add-in which returns an XLOPER will automatically work with the dynamic array behaviour? Can I effectively recreate a "FILTER" or "SORT" function inside an add-in or do these functions themselves have internal magic too? Is "SINGLE" special or can I opt into implicit intersection behaviour from inside my array-returning UDF function by calling SINGLE via the C API?
  • Can dynamic arrays be used inside Tables? Currently array functions cannot be put in Tables. It would be great to have the auto-expanding and shrinking Table behaviour anchored on a synamic array function.
  • How does the # spill operator impact the reference passed to a function taking an XLOPER reference parameter in the C API? Is there any way to know that a parameter is a dynamic array (as opposite to a fixed, explicit range)?
  • How is the caller identified for dynamic array functions (via xlfCaller in the C API or Application.Caller in VBA)?
Copper Contributor

This is awesome! I've been hoping that Excel would add the UNIQUE and SORT functions that have been available in Google Sheets, but I am even more impressed that Excel has done far more than just adding these functions. I can't wait to see what I can do with the new A1# notation (I'm really hoping it can be used in data validation dropdowns). And the SEQUENCE function is brilliant - ought to make creating advanced formulas easier (and hopefully without being volatile).

Silver Contributor

Awesome!

But, I hope you update Excel 2019 to include these new features!

Please do not just limit them to Excel for Office 365.

Copper Contributor

 Loving the new spill concept and new functions. Will vastly improve dynamic named ranges so that no longer is it an offset with a calculated height and width but just $F$5#.

 

And thank you for native UNIQUE and SORT functions. I can't wait to retire my Excel VBA QuickSort / Unique library plus this reduces the number of functions I need to rewrite in the Javascript API, so double points.

 

 

Bronze Contributor

Does someone that has these know if you can wrap other functions around them? For example, would =COUNTA(UNIQUE(A1:A10)) return the number of unique items in A1:A10?

The old way with the CTRL-SHIFT-ENTER would involve something like =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)

Copper Contributor

So then, to get a concatenated list of the unique values in some range, just embed UNIQUE inside TEXTJOIN?

 

Brilliant!

Microsoft

@Ed Hansberry we're glad you like the look of the feature! Yes you can wrap functions. =COUNTA(UNIQUE(A1:A10)) will work as expected.

 

@Emma Bailey @Phil Trick Thanks! Looking forward to getting it out.

 

@Jon Wittwer It can be used with Data Validation. DV + UNIQUE + A1# = :ok_hand: 

 

@Haytham Amairah Dynamic arrays will not be in Excel 2019. Excel 2019 has just been released and this feature is only just making it into O365 insiders.

 

@Patrick Matthews Sure can! 

 

@Govert van Drimmelen Great questions! I'll follow up with the answers next week. 

Really powerful new feature,  absolutely loving it.     

Dynamic drop down lists are now simple

Many uses of Pivot Tables are now easily replicated in automatically refreshing formulas

Well done Excel Team!

Copper Contributor

@JoeMcDaid What would be the official way of checking whether an Excel instance hosting my add-in supports dynamic arrays? Is there a first Excel version that supports dynamic arrays and any later version will too, or will this be selectively enable depending on other factors? How should I check from inside my add-in?

Bronze Contributor

Who do I have to bribe to get them to enable this on my Fast Insider build? I can send money via paypal, venmo, apple pay, etc. :)

Copper Contributor

I would love to know the roll-out timetable, I'm unfortunately not on the insider list but want to get all my work colleagues tee'd up at the right time to change all of our many spreadsheets - so much complexity can be removed, so much storage space can be saved!

Bronze Contributor

Is anyone seeing a lot of formulas being wrapped with SINGLE() after getting the new functions?

 

I have a number of spreadsheets that return a single row table from Power Query, and then I refer to those tables in other formulas. So =TableName[FieldName] would give me the count of rows in a query, or the total of a value, etc. Usually used for error checking. One of my files this morning had 7 SINGLE() functions in it. some simple as above, some deeply nested in those stupidly long uneditable formulas I am prone to do over time.

 

Now those are all showing up as =SINGLE(TableName[FieldName])

 

Creating a new single row table in a new file, typing =Table2[data field] does not cause it to wrap in SINGLE(), however.... if I create the formula in Excel Online or an older version of Excel, =Table1[Test data] becomes =SINGLE(Table1[Test data]) when I open it in the new version.

 

EDIT: Is this the right place to discuss issues or questions about these new functions?

@Ed Hansberry, I guess that's due to algorithm of the conversion of old style expressions to new one. That's like ={1,2,3} in current Excel is converted to first element in background and returns 1. New Excel will return spill for the same formula. To return only first element we shall use =SINGLE({1,2,3}).

 

When we open in new Excel the file with such formula created in classic Excel it shows such conversion with SINGLE.  In classic Excel "single" is somewhere in background and not visible.

 

Table[Column] is an array which is returned to the range. For one element only it could be converted into one cell range and we see something as above.

 

All above is only my guess, I didn't dig too deep with investigations and have no Microsoft knowledge of things.

Bronze Contributor

Thanks @Sergei Baklan - makes sense, and was along the lines of what I was thinking. I think this will cause a bit of initial confusion, and wonder if the conversion from old to new should be smarter and not put =SINGLE() every possible place. 

 

if =TableName[Field] gives same result as =SINGLE(TableName[Field]) then don't wrap it with SINGLE().

 

I suspect this is one of the biggest changes Microsoft has done with Excel in recent memory that has such implications to not change how calculations are expected to work as files are upgraded to the new calculation engine.

Bronze Contributor

Wondering if this early in the process there is still time to get changes to these dynamic array functions. I think a super useful argument to these would be a TopN optional argument.

 

So =SORT(A1:A100,,,,10) would sort the data in A1:A100, but only return the top 10. The only way I can see to do that is to put the =SORT() funcition in Cell C1, highlight C1:C10, then edit, and old-school CSE the thing to a hard array.

I might be able to figure out a way nesting FILTER() and SEQUENCE(), but a TopN argument would make it so simple.

 

Oh wait. This works. Put some random text in R3:R20. This will return the top 10 items. this is ugly, with a capital U.G.L.Y. But... it isn't a CSE formula. I'm sure I could clean this up as I threw this together in about 10min, but still. TopN dude....

 

=LEFT(FILTER(R3#&SEQUENCE(10,1,0.00001,0.0001),NOT(ISNA(R3#&SEQUENCE(10,1,0.00001,0.0001)))),FIND(".",FILTER(R3#&SEQUENCE(10,1,0.00001,0.0001),NOT(ISNA(R3#&SEQUENCE(10,1,0.00001,0.0001)))))-2)

Microsoft

@Ed Hansberry Great to hear you now have access!

 

Old Excel would silently apply implicit intersection logic to its formulas to force single value results. Dynamic Array Excel no longer needs this as it can output arrays natively. So instead of implicitly intersecting, DA Excel calls out where this could have happened in old formulas using the SINGLE function. In your example, =TableName[Field] is a  full column reference that only has one value but if you ever added a new row to that table, that very same reference would return multiple values and, in old Excel, it would have triggered implicit intersection. To ensure your formula continues to operate as originally authored, we show where implicit intersection would take place using SINGLE. You are free to remove the SINGLE from your formula if you do not want implicit intersection to apply.

 

TopN sounds like a great function candidate. Our initial focus is on getting Dynamic Arrays out with the 1st wave of functions but we are looking forward to unlocking more scenarios with future functions. If there is a function you'd like to see, please add it to uservoice so that we have a place to discuss the design and the community can vote. 

Copper Contributor

Can these functions be used in the Criteria Range of an Advanced Filter

Sam

Bronze Contributor

Done @JoeMcDaid  - https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35667973...

 

I wasn't thinking of a separate TopN function, just an optional parameter in the Dynamic Array functions that it would make sense for. SORT and FILTER could definitely use it for example.

Thank you @Ed Hansberry, voted

Bronze Contributor

So I guess #CALC! is a new error type when the arrays go awry?

 

Actually looks like #UNKNOWN! and #FIELD! are also new for Array errors. This page needs to be updated. I'd like more explanation on what these error types are to trap things with a bit less trial and error.

Bronze Contributor

I think this is a bug with SEQUENCE().

  1. Create a table with 10 rows not including the header.
  2. Have at least 2 columns, say Col1 and Col2
  3. Fill column 1 with jibberish.
  4. Put this in the first row of Col2: =SEQUENCE(ROWS([Col1])

It returns #SPILL! instead of a sequence of numbers that matches the number of rows in the table. ROWS([Col1]) is correctly returning 10. Replace ROWS() with 10 and it works, but it is no longer dynamic.

 

I submitted a frown face for this.

 

Yes, #CALC! is a new one and not documented yet

By the way, =LARGE(array,SEQUENCE(N)) works as TopN

image.png

@Ed Hansberry, how did you receive =SEQUENCE(10) in the table without errors? SPILL doesn't work with tables https://support.office.com/en-us/article/-spill-error-table-formula-8bfa1758-1be4-42f4-a974-c4d4bc51...

Bronze Contributor

Hrm.... Now I am trying to recreate what I did. SEQUENCE(1) works, but not anything >1. Maybe I was doing some inside of and some outside of a table.

 

As for your LARGE() function. Very nice. Much nicer and cleaner. 

Copper Contributor

1. Can the new array formulas be used in the Criteria Range of an Advanced Filter 

2. Can SORT be used as below to take advantage of the Binary search option

MATCH(B1,SORT(A1:A10),1)

Copper Contributor

Hi,

I Can't see Functions like Sequence, Filter in Office 365 Insider! Could anyone please help here?

 

Regards,

Mohit

Hi @Mohit Verma, not everyone gets everything straight away in Office Insiders Fast,  things are gradually released.

 

It can be frustrating having to wait

Copper Contributor

Hi @Wyn Hopkins.. Thanks for the update! I will be looking forward to get this update. Super excited to explore new features! :)

 

Regards,

Mohit Verma

Bronze Contributor

@sameer bhide

  1. The array functions can be used in the criteria section of the Advanced Filter feature, but the new array functions will return results that dynamically expand and contract, so make sure the range in the criteria will dynamically expand as well. By default it uses absolute references.
  2. You can use the arrays in a Match, though in your example, sorting isn't necessary to match. But it would work great with a FILTER() for example.
Bronze Contributor

Does anyone know a way to find Excel files in Office 365 with CSE formulas in them? Searching for { and ={ returns nothing or in the case of Delve, errors. I'd love to see what has been done in the org over the past 2 decades and see what can be replaced with the new functions.


Bronze Contributor

Hmmm... so in New Excel,

 

=SUM(A1:A10*B1:B10) replaces =SUMPRODUCT(A1:A10,B1:B10)

 

Will SUMPRODUCT() and other functions that ignore implicit intersection be depreciated as existing functions start ignoring it as well - where you'd need to use SINGLE() to continue using it? (Not dropped, but where things like CONCATENATE() are not encouraged as CONCAT() is the new way to do it.)

Copper Contributor

How do I join the office insider program?

I am a Mac user with an office 365 subscription. 

Thank you

Copper Contributor

Outstanding!

 

Will I be able to treat Pivot Tables as a Dynamic Array? Right now, pivots don't offer a built-in way to reference them in formulas



 

Copper Contributor

Hmmm...

Is there any way to set up a grand total formula and top-border format immediately below a spilled range? Or are we going to have to accustom the world to our placing aggregates above a list of data, rather than below it?

Iron Contributor

this new fnctions are a good addition however I can not find them in Excel 2019. Where can I find them? Or are they only available for Excel 365?


Bronze Contributor

@Charley Kyd - this really highlights the need to allow dynamic array functions to work in Excel Tables, causing them to expand as necessary to handle the results. 

Microsoft

@erol sinan zorlu Dynamic Arrays will not be coming to Excel 2019, only Excel 365. 

@Ed Hansberry No plans to deprecate SUMPRODUCT, it has its advantages. Many users find it more readable/descriptive than SUM(A1:A10*B1:B10)

Iron Contributor

Well I was expecting Excel 2019 would be a massive step forward however missing these dynamic array formulas it seems to be a face lift of 2016. It is a pity to hear also there is no plan on implementing this function to it. I am very dissapointed right now and see no reason to upgrade it from 2016.


Bronze Contributor

@erol sinan zorlu - You can see here what is new in Excel 2019 over 2016. The Dynamic Array functions will no doubt be in the next version of Excel (2022 or whatever) but right now it isn't even in Office 365 yet, just insider builds.

 

That is the problem with the perpetual license. You get what you get for when it was released. My understanding is Excel 2019 is effectively the 1803 build of Excel from Office 365, so what Office 365 users generally had in March of 2018. Then I suppose they spent those 6 months really fixing any bugs in 1803 before slapping the 2019 sticker on it.

 

It is what Paul Thurrott calls "Getting Yesterday's Office Tomorrow"

Copper Contributor

Although dynamic arrays are good in principle, the current implementation has two features that cause significant problems for current users of array functions.

1.  I use UDFs returning arrays extensively.  Often I only need the top left value, or some part of the return array.  If the whole array overlaps some existing data, these functions now return SPILL. 

2.  It seems that sometimes the new Single function is applied automatically.  Presumably this is not compatible with earlier Excel versions.

 

Rather than deprecating use of Ctrl-Shift-Enter, why not incorporate it in the new functionality?  I suggest:

1. If the function is entered with the Enter key, it will return the entire array, or SPILL, i.e. as in the current version.

2. If it is entered with Ctrl-Shift-Enter it will return a single cell, or whatever range is selected (or SPILL if the selected range contains any data).

3. Allow the returned range size to be reduced in extent, as well as increased, when entering with Ctrl-Shift-Enter.

4. Allow conversion between the two behaviours by re-entering with Enter or Ctrl-Shift-Enter.

Bronze Contributor

@Doug Jenkins

  1. Can you wrap your function in LARGE() to return only the topN results to prevent it from returning too many rows? See Sergei's example here.
  2. The =SINGLE() function will automatically show up sometimes in 1811 when it appears that implicit intersection is happening, but when you reopen the file in a version of Excel that doesn't understand Dynamic Array functions, the =SINGLE() is removed. See Joe's explanation of what is going on here.
Copper Contributor

Ed - yes, Large() will work, but it's a huge amount of work to wrap Large around every function where it might be required.

 

Regarding the Single() function, I just checked today (25th Oct), and it doesn't seem to work as you said:

I have a spreadsheet using the same UDF to return a series of columns.  As currently working the function is copied down each column and is not entered as an array function.  Each instance of the function returns a 1D array, but only the first value is displayed.

  1. On opening in Excel with dynamic arrays everything displays correctly, and the Single() function has not been applied.  I don't know if this is a change in functionality, or if the Single() function is only applied under different conditions.
  2. If I change any input values all the functions recalculate correctly.
  3. If I press F2 then Enter with any function, it returns SPILL.  Undo will return the original display, but obviously removes any edits.
  4. If I press F2 then Ctrl-Shift-Enter the function displays correctly (with {} in the Edit box).
  5. If I wrap the function in Single() it displays the first result when entered with Enter.
  6. If I save and open in Excel without array functions the Single()  functions display as #NAME, with the function preceded by _xlfunc.

In summary:

  1. The Single() function seems redundant, since you can get the same effect with Ctrl-Shift-Enter, which will also work in previous versions.
  2. Using Ctrl-Shift-Enter also allows a function to be edited, and/or a partial array to be returned.
  3. Rather than deprecating Ctrl-Shift-Enter, it would greatly enhance the use of dynamic arrays if it was retained as an active feature, and was also enhanced to allow the size of the array output range to be reduced, as well as increased.

 

Bronze Contributor

FYI - for those that also use the Mac version of Office 365 on the Insider Fast ring, these Dynamic Array functions showed up on my Mac Friday when I updated the latest build. Smiley Happy

 

Anxiously awaiting the Online version of Excel to support this.

Copper Contributor

@joe

Here are some interesting ways of using Dynamic Array Formulas

Sample File

https://1drv.ms/f/s!AiKBTsYfZw-vgrtkdDXnLzxgimxokg

 

1. Multi Criteria SUMIFS / COUNTIFS

=SUMIFS(D[COST],D[CLIENT],INDEX(N2#,,1),D[REGION],INDEX(N2#,,2))

2. Multi Column Sort

=SORT(D,{1,2,3,4},{1,1,1,-1})

3. Sort by Custom List e.g North, South, East, West or Jan, Feb, Mar..

=SORTBY(D,D[CLIENT],1,MATCH(D[REGION],CustList,0),1,D[COST],-1)

CustList is either a named Range containing North, South, East, West or an array Constant

4. Emulate Wild cards in the Filter Function

a) Contains (*Text*)

=FILTER(D,IFERROR(SEARCH(K1,D[CLIENT]),0),"No Data")

b) Begins with (Text*)

=FILTER(D,IFERROR(LEFT(D[CLIENT],LEN(Q1))=Q1,0),"No Data")

c) Ends with (*Text)

=FILTER(D,IFERROR(RIGHT(D[CLIENT],LEN(V1))=V1,0),"No Data")

5. Clients with Characters (=???)

=FILTER(D,IFERROR(LEN(D[CLIENT])=LEN(AC1),0),"No Data")

6. Extract a user defined number of Random Records from a Dataset

=INDEX(D,RandIndex,SEQUENCE(,COUNTA(D[#Headers])))

Where RandIndex = NDEX(SORTBY(SEQUENCE(ROWS(D)),RANDARRAY(COUNTA(SEQUENCE(ROWS(D))))),SEQUENCE(NoOfSamples))

7. Multiple OR Criteria

a) Clients : ABB or BHEL or ORACLE

=FILTER(F,COUNTIFS(mClients,F[CLIENT]))

b) BU : US or DE or GB

=FILTER(F,MMULT(IFERROR(--(SEARCH(TRANSPOSE(mBU),F[BU])=1),0),SEQUENCE(COUNTA(mBU),,,0)))

c) Cost (> 10 and < 20)  or (>90 and <100)

=SORT(FILTER(F,((F[COST]>10)*(F[COST]<20))+((F[COST]>90)*(F[COST]<100))),5)

8. Remove and Rearrange Columns

=INDEX(F,SEQUENCE(ROWS(F)),{3,5,1})

 

Enjoy

Sam

Iron Contributor

=SEQUENCE(n) is very nice new function

 

till today I used for this following =ROW(OFFSET($A$1;0;0;n;1)) when needed. One example:

=IF(ISERROR(TEXT((CODE(MID("FEDCA@";LEFT(A11;1);1))-50)*1000000+LEFT(A11;7);"0000\.00\.00")+0);"isikukood vigane";IF(IF(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("3456789123";COLUMN($A$1:$J$1);1)+0));11);10);MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11))=MID(A11;11;1)+0;"isikukood õige";"kontrolljärk vale"))

this is to validate Estonian Personal ID - now I can rewrite this to shorter.

One question too:
Is there any idea or plan to allow dynamic arrow formulas to be used in TABLES. In same table (like =SORT) or on another table (like =UNIQUE)

for example - currently I have to "sort" the names on table column use following practice:
1. add  column RANK:=

=COUNTIF([Name];"<"&[@Name])+1


2. then add column SortedName:=

=INDEX([Name];MATCH(ROW()-ROW(Table1[[#Headers];[SortedName]]);[Rank];0))


Dynamic arrays will help this a lot

Iron Contributor

Actually I find solution how to add sorted column into table using funktsioon =SORT()

 

I have Table (Table1) and column Name in this

I create column SortedName with following formula

=INDEX(SORT([Nimi]);ROW()-ROW(Table1[#Headers]))

WORKS!


Steel Contributor

So I'm having trouble understanding why the below doesn't work.

I have an Excel table (just called Table1) with one column called "Number". In it are 6 item numbers.

Number

a123456
a123444
a000013
a004025
a004023
a123457

 

I have the following formula in E2:  =LEFT(Table1[Number],6)
Then I have another formula in F2: =COUNTIFS($E$2#,LEFT(Table1[@Number],6))

 

That formula I copy down from F2:F7. Works perfectly. It tells me if the first 6 digits of the 7 digit item numbers are repeated. The numbers a123456 and a123457 would show repeats because the first 6 digits, a12345, are repeated 2x in the list.

 

However, if I replace the $E$2# reference with the LEFT(Table1[Number],6) function, Excel tells me there is an error with the formula and won't let me enter it.

 

Is there any way with Dynamic Arrays to get this to be a single formula without this "helper formula" in E2#? I've tried COUNTIF() as well, and no matter what I do, I cannot get it to accept an array from a function, but it does from a #SPILL reference.

Version history
Last update:
‎Oct 05 2020 09:49 AM
Updated by: