Array Functions

Question

I am currently learning the new array functions in Excel. I've been learning them for a bit, but still learning.

I am trying to create a dynamic YTD type report off some dummy data however I can't seem to get it to work as I'd hoped.

I have data in A1:B14, dummy dates (A) and values (B), 12 are 1st of the months 1-12, 2 are dates in the month to test the next stage.

In D1, i have number from 1-12, showing the January to x months.

So far I have got:

=LET(
_data,$A$1:$B$14,
_months,SEQUENCE(,$D$1,1,1),
_dates,DATE(2025,_months,1),
_results,(CHOOSECOLS(FILTER(_data,CHOOSECOLS(_data,1)=DATE(2025,10,1)),2)),
_totals,(BYCOL(_results,LAMBDA(a,SUM(a)))),
VSTACK(_dates,_totals))

Which correctly gives me the sum in once cell for the 1st October value, however, I am getting in a mess when trying to use _dates, instead of the hard coded date, it gives a #value error.

_results,(CHOOSECOLS(FILTER(_data,CHOOSECOLS(_data,1)=_dates),2)),

As mentioned, I am learning these so this data is dummy data.

I would like this to show a sum for each month, equivalent of SUMIF dragged across.

My gut tells me that you're making it more complicated than it should be. Are you familiar with the GROUPBY function? — kb2136
– kb2136, Commented Oct 27 at 13:12
Thank you all. I agree, it does sound like I'm going a more difficult way. I'm working through learning this whilst i have other things running, so trying to take a logical approach to the explanations on the net, and use in a real world-ish problem to solve. So its more the formula I'm learnning to apply to other situations when i can. :) — Nathan_Sav
– Nathan_Sav, Commented Oct 27 at 13:32
It's better not to explain your data, but post a markdown table of it ( tablesgenerator.com/markdown_tables ) along with expected result with given data. Now you get different answers of which noone knows if it's what you mean. What did you mean by "2 are dates in the month to test the next stage." Are they to be included or excluded of the sum? So you want the month totals, or the first of month totals? What's the next stage? — P.b
– P.b, Commented Oct 28 at 14:32

Mayukh Bhattacharya · Accepted Answer · 2025-10-27 13:32:30Z

2

Here is what you could try using the following formula:

=LET(
     _data, DROP(A:.B, 1),
     _months, SEQUENCE(, D1),
     _dates, DATE(2025, _months, 1),
     _totals, BYCOL(MONTH(_dates), LAMBDA(x, 
     SUM(FILTER(CHOOSECOLS(_data, 2), MONTH(CHOOSECOLS(_data, 1)) = x, 0)))),
     VSTACK(_dates, _totals))

Or, Using GROUPBY() perhaps:

=LET(
     _a, A2:A15,
     _b, MONTH(_a),
     TRANSPOSE(DROP(GROUPBY(HSTACK(_b, DATE(2025, _b, 1)), B2:B15, SUM, , 0, , _b<=D1), , 1)))

Using the XMATCH() + ISNUMBER() function to select the required array

=LET(
     _a, A2:A15,
     _b, MONTH(_a),
     TRANSPOSE(DROP(GROUPBY(HSTACK(_b, DATE(2025, _b, 1)), B2:B15, SUM, , 0, , ISNUMBER(XMATCH(_b, SEQUENCE(, D1)))), , 1)))

edited Oct 27 at 13:32

answered Oct 27 at 13:26

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Odesola · Accepted Answer · 2025-10-27 13:25:01Z

1

=LET(
  _data, $A$1:$B$14,
  _months, SEQUENCE($D$1,1,1,1),
  _dates, DATE(2025, _months, 1),
  _monthVals,
     MAP(
       _dates,
       LAMBDA(d,
         SUMIFS(
           INDEX(_data,,2),
           INDEX(_data,,1), d
         )
       )
     ),
  VSTACK(_dates, _monthVals)
)

answered Oct 27 at 13:25

Peter Odesola

113 bronze badges

1 Comment

Community Oct 27 at 13:43

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Ron Rosenfeld · Accepted Answer · 2025-10-27 17:02:34Z

1

Hard to know exactly what you want for output, as you did not supply sample data or a desired output, but if A&B contain your dates and values, a simple PIVOTBY should produce results:

Given Data:

You can use this formula:

=PIVOTBY(YEAR(A2:A24),MONTH(A2:A24),B2:B24,SUM,0,0,,0)

to get these results:

answered Oct 27 at 17:02

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

Array Functions

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related