Final Friday Fix: October 2023 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

Given an array of numerical values separated by categories (with some duplicates) and periods, this month we want you to find the combined value of each item for each period with the shortest formula possible.  There are straightforward ways to do this using SUMIFS by each column or SUMPRODUCT, but we’re challenging you to do it for the whole array in one [1] formula – which spills – using dynamic arrays.

You can download the question file here which contains the following array and a space to put your answer, which should look like the following:

Some requirements:

• you are only allowed one formula entered into one cell only
• no LAMBDA or LAMBDA helper functions (e.g.  LET, BYROW or MAP) are allowed
• no Power Query or VBA allowed; this is a formula challenge.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!