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.

The problem

In this simple example we want to add up the values in column B if column A equals WA or VIC.

There are at least three solutions – they are shown in cells F2, F3 and F4. The formulas used in column F are shown in column G.

You can download the file at the bottom of the post.

The issue with the SUMIFS solution is that it is not scalable. Every new option requires a new SUMIFS function to be added to the formula.

=SUMIFS(B2:B19,A2:A19,D2)+SUMIFS(B2:B19,A2:A19,E2)

The SUMPRODUCT solution involves what is called array syntax, where you use braces (curly brackets) to enclose multiple options separated by commas.

=SUMPRODUCT((A2:A19={"WA","VIC"})*(B2:B19))

The limitation with the SUMPRODUCT solution is that the entries have to be manually keyed in. To increase the number of options you have to edit the formula.

To link to cells you can use the INDEX function with array syntax.

=SUMPRODUCT((A2:A19=INDEX(D2:E2,{1,2}))*(B2:B19))

Again, this is not flexible. If we need to add another state, we still need to edit the formula to allow an extra state.

There was no way to link to cells within the array syntax which would have made it more flexible. Dynamic arrays have changed that.

Dynamic arrays

I originally thought of using the UNIQUE function, but found the SORT function works just as well.

Both these functions return arrays. They usually SPILL their entries across other cells, but when used with SUMPRODUCT they create an array that is linked to cells.

=SUMPRODUCT((A2:A19=UNIQUE(D2:E2))*(B2:B19))
=SUMPRODUCT((A2:A19=SORT(D2:E2))*(B2:B19))

This creates a flexible and scalable solution.

Both these solutions handle adding an extra state without having to edit the formulas.

I have inserted a column between D and E and added QLD in cell E2.

As you can see the UNIQUE and SORT solutions both updated.

=SUMPRODUCT((A2:A19=UNIQUE(D2:F2))*(B2:B19))

=SUMPRODUCT((A2:A19=SORT(D2:F2))*(B2:B19))

The other three need to be edited to handle the third option.

It seems SUMPRODUCT will be remain useful even with the power of dynamic array functions.

Download example file

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.