# Solving a Conditional Summing Text Problem in Excel

I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.

# A New Month of Sundays

One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.

# Dynamic List of Weekday Dates in Excel

Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.

# Introduction to Dynamic Arrays Webinar Recording

Learn about the new way to create formula and functions in Excel. This webinar recording from April 2024 will get you started.

# Excel Dynamic Arrays in April

## April is Dynamic Array month.

This month I ran 4 new live one hour webinars dedicated to all things Dynamic Arrays.

Dynamic Arrays change the way you create and maintain formulas in Excel. They expand Excel’s capabilities and make it even more flexible.

All four webinars are now online courses that you can buy as part of the Dynamic Arrays 2024 Bundle for AU\$60. PLUS you get access to future Dynamic Array sessions this year.

As new Dynamic Array courses are added to the Bundle the price will rise during the year. Buy now to get the best deal. Use the button below to see more details and buy the Bundle.

Note: you need the subscription version of Excel to use Dynamic Arrays.

# Does an Excel range contain sequential numbers?

I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.

# Extracting Initials in Excel – Part 2

In the previous post we looked at extracting initials when we only had a first name and a last name in this post, we will look at handling more than two names.

# Extracting Initials in Excel – Part 1

Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.

# Macro to Format a Spill Range in Excel

### VBA and spill ranges can work together

Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.

# Excel Custom Function for a List

Excel’s Custom Lists are great, but you need to drag them to create them. If you have a list that you use frequently why not create a custom function to display it? This has the added advantage of creating a spill range that can then drive other dynamic array formulas.

# Benford’s Law in Excel – Part Two

Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In my previous post I created a report to analyse a data set based on Benford’s Law. In this post we will create a single formula to create the report and then convert that into a custom function.

# Benford’s Law in Excel – Part One

Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In actual data sets when reviewing values the 1st digit of the values tends to follow a predetermined frequency. For example, roughly 30% of the values should start with a 1.

# SUBTOTAL and Dynamic Arrays in Excel – Part 4

The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will amend the custom function we have created to add an extra column plus headings.

# SUBTOTAL and Dynamic Arrays in Excel – Part 3

The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will create a custom function to summarise a data set.

# SUBTOTAL and Dynamic Arrays in Excel – Part 2

The SUBTOTAL function in Excel is quite flexible and in this second post we build an automated summary report using SUBTOTAL.

# SUBTOTAL and Dynamic Arrays in Excel – Part 1

The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. It can also ignore hidden rows, something that not many Excel functions can do.

# Excel Custom Function to Return Unique Random Whole Numbers

In last week’s blog post I covered a complex formula to return unique random whole numbers. In this weeks’ post we will look at how we can convert that complex formula into a custom function.

# Unique Random Whole Numbers in Excel

Excel has three functions that can provide random numbers. But the random numbers created may not be unique random whole numbers. Here is one way to get a list of unique random whole numbers.

# Custom Calendar Function in Excel

A few years back I posted a series of three posts about developing a formula to create a month calendar in Excel. I thought I would revisit that and convert that long formula into a custom function using LAMBDA.

# Extract Text Before a Number in Excel

Excel’s new TEXTBEFORE function simplifies extracting text from the left. In this example I share how to extract all the text before a number in a code.

# Forcing a Spill with the Magic Plus Sign

I posted recently about a technique to force a function to spill if it didn’t spill automatically. I have since learned of a much easier way.

# One Minute to Excel #30 – Extract Data Based on Sheet Name

### Shortcut to speed up name creation

Here’s a technique I use a lot to speed up report development.

Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.

This short video combines a few techniques to extract from a data set based on the sheet name.

All in less than a minute.

# Array Syntax in Excel Formulas

With dynamic arrays making array calculations more accessible and easier to use here is a hack for using array syntax in Excel formulas.

# UNIQUE Function and Blank Cells in Excel

### Zero in on a problem

The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.

# Adding up Text Numbers In Excel Another Technique

If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.

# One Minute to Excel #24 – 1,000 random dates

### A RANDARRAY solution

Let’s say we need to do some testing and we need 1,000 random dates in 2022.

We can use a new function to make this easy to create and easy to change.

RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.

I set myself a challenge to do this in less than minute – see how I went in the video below.

# Switching Reports from Rows to Columns in Excel

### TRANSPOSE and OFFSET solution

I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.

# Dynamic Arrays and a Book Index

### Another solution

Years back when I wrote my Excel book, I had to create an index for the book. I shared the file I used including the macro in this post. Recently I thought dynamic arrays could do much of the work for this.

# Let’s TRIM with Dynamic Arrays in Excel

### Removing problematic spaces with a single function

Dynamic arrays allow you to use a function normally built to handle a cell, with a range of cells. The TRIM function can remove extra space characters in cells. So with dynamic arrays it can handle ranges.