Sum multiple rows/columns individually (with one formula) in Excel

Summing a set of rows or columns individually used to only be possible using a formula for each. That changed with the release of the BYROW and BYCOL functions last year.

In the example, a sales table lists six products vertically and the months of January, February and March horizontally.

To return the total sales for each product, traditionally, we'd use a SUM formula for each row, e.g. =𝚂𝚄𝙼(𝙱𝟹:𝙳𝟹), =𝚂𝚄𝙼(𝙱𝟺:𝙳𝟺), =𝚂𝚄𝙼(𝙱𝟻:𝙳𝟻).

By modern standards, this is bad practice, as more formulas means more chaos. Just imagine having hundreds or thousands of products. 😬 More to go wrong! ⚠️ ⛔️

Instead, a single formula that spills does the same job:

=𝙱𝚈𝚁𝙾𝚆(𝙱𝟹:𝙳𝟾,

𝙻𝙰𝙼𝙱𝙳𝙰(𝚂𝚊𝚕𝚎𝚜,
𝚂𝚄𝙼(𝚂𝚊𝚕𝚎𝚜)
)

)

Inside BYROW, we reference the entire range in 𝚊𝚛𝚛𝚊𝚢 and in [𝚏𝚞𝚗𝚌𝚝𝚒𝚘𝚗] define a LAMBDA called Sales. SUM then uses this to add up each row.

Conversely, we can use BYCOL to total up the sales for each month with an almost identical formula:

=𝙱𝚈𝙲𝙾𝙻(𝙱𝟹:𝙳𝟾,

𝙻𝙰𝙼𝙱𝙳𝙰(𝚂𝚊𝚕𝚎𝚜,
𝚂𝚄𝙼(𝚂𝚊𝚕𝚎𝚜)
)

)

#exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on:

[A_Dudkin]

This is no such function in the regular Excel