It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.
Category Archives: Dynamic Arrays
How to Refer to a Column in a Spill Range
INDEX rules
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
Single formula for a Column
It can done
In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.
XLOOKUP Doesn’t Always Spill
But it can
The new XLOOKUP function has the ability to spill when you select multiple columns to extract. Even when you do, it doesn’t always spill across.
Possible Dynamic Array Bug
Values issue
Recently I have been using more dynamic array formulas and have come across an unusual situation where a date is not treated like a date.
Remember dynamic arrays are currently only available in the subscription version of Excel.
Have a look at the image below.
You can download the example file at the button at the bottom of the post if you want to see it in practice.
Cell B1 is an input cell to set the first date of the sequence.
Cell E1 creates sequential numbers using the SEQUENCE function.
Row 2 has the dates and is formatted as a date.
Row 3 is the problem. If I try calculate the number of calendar days in the month using DAY and EOMONTH functions it gives a value error as if the entry in E3 is not a date.
If I multiply the E2# reference by 1 it fixes the issue as per row 4, but I shouldn’t have to do that.
I have had this is other cases where a number is not treated a number until you multiply it by 1 or perform a calculation with it.
Not sure if anyone else has seen this?
If you want to learn more about dynamic arrays I have a free Webinar Recording plus a pdf manual at the link below.
Free Introduction to Dynamic Arrays Course
Related Posts
Sequences of a Repeating Series of Numbers in Excel
MOD and SEQUENCE used together
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
Summing Text Numbers in Excel Updated
Dynamic arrays to the rescue
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
How to Create Linked Array Syntax in Excel
It is now possible
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
Using SEQUENCE with Dates in Excel Part 3
How to highlight specific dates
In this post I finish off the Calendar matrix by adding holidays.
Using SEQUENCE with Dates in Excel Part 2
Time to enter the matrix
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
Using SEQUENCE with Dates in Excel Part 1
Date listing
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Time to Play with Dynamic Arrays
Let play time begin
Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.
Switching Rows and Columns is Now Easier
TRANSPOSE rules
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.
The End of SUMPRODUCT
Dynamic arrays will carry it off in to the sunset
The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
New Formula Symbol in Excel
The @ is re-purposed and the # is new
With introduction of Dynamic Arrays in Office 365 Excel has one new formula symbol and another that was previously only use in formatted tables.
New Dynamic Array Function SEQUENCE
Part One - its all about spilling
Dynamic arrays have the potential to change the way Excel spreadsheets are created. They were released in the January 2020 wave of updates to the Office 365 subscription version.