When I first started to use dynamic array functions in my models, I often spent an embarrassingly long time troubleshooting why my formulas weren’t working, and even longer to work out how to fix them. That is until I realised that in almost all cases the answer was to use BYROW and BYCOL.

To save you the time I wasted, let me introduce these functions to you.

Example 1: Calculating Totals by Columns and Rows

I’ll start with a simple example. I have a calculated block of numbers in E15:K18 which is generated by a spilled formula in cell E15:

Now, I want to add a total row beneath it to calculate the indexed costs by year. My initial thought is to use ‘=SUM(E15#)’. However, this doesn’t produce the desired result - it sums up all the numbers in the block, giving a grand total instead of annual totals.

Enter BYCOL. By using ‘=BYCOL(E15#, SUM)’, I can generate the correct total row. 

In this case BYCOL instructs Excel to do a sum BY COLumn.

Conversely, if I want totals by rows - for example, to find total costs per type - I could use ‘=BYROW(E15#, SUM)’, which calculates the total of the block BY ROW.

BYROW and BYCOL combined with LAMBDA

Whilst BYROW and BYCOL support several functions as their second argument - such as SUM (used in the example above), COUNT, MIN, MAX, and AVERAGE - they can also take a LAMBDA function. This significantly expands their versatility and means they can solve even more difficult problems.

That said, while LAMBDA provides extraordinary flexibility, its syntax can appear daunting, especially to those unfamiliar with it. However, mastering this function is well worth the effort, as combining LAMBDA with BYROW or BYCOL unlocks powerful customization options.

To illustrate the LAMBDA syntax, here’s how the earlier BYCOL formula would look with LAMBDA:

‘=BYCOL(E15#,LAMBDA(col,SUM(col)))’

In this formula:

• The first argument of the LAMBDA function defines the name of the column variable. I tend to use "col" for clarity, but you can use anything you want.
• The second argument specifies the operation to perform on each column - in this case, summing the values in the column.

Similarly, with BYROW, the LAMBDA function’s first argument represents the row variable name, while the second argument defines the operation for each row.

For more explanation on LAMBDAs check out this Excel community article from our archive.

Example 2: Using BYROW to fix a #N/A error

Let’s move onto my second example. I have a table listing customers, products, and order values.

In F3, I’ve created a unique list of products. In column G, I want to generate a comma-separated list of customers for each product. I add the formula ‘=ARRAYTOTEXT(FILTER(Table1[Customer Name],Table1[Product]=F3))’ and test if it works for the first product in my list in F3:

Success!

However, replacing F3 with F3# to apply it to the entire list results in a #N/A error, with an unhelpful error message.

Once again, BYROW comes to the rescue. Wrapping the original formula in LAMBDA and using it within BYROW delivers the desired result:

‘=BYROW(F3#,LAMBDA(row,ARRAYTOTEXT(FILTER(Table1[Customer Name],Table1[Product]=row))))’

Final Thoughts

Next time you’re troubleshooting dynamic array formula issues don’t forget about BYROW and BYCOL. These unsung heroes can save your day, your spreadsheet, and perhaps even your sanity.