#39 — Define A Dynamic Range of Columns And Perform Aggregation

Problem description & analysis:
In the following Excel table, columns are months arranged in order and their displayed names are representations of the corresponding dates. For example, Jan represents 1/1/2023.

We want to sum values within the interval defined by the start month and end month parameters defined in G1 and I1.

Solution:
Use SPL XLL to do this:

=spl("=p=(d=?1)(1).pselect@a(between(E(~),E(?2):E(?3))),d.to(2,).(~(p).sum())",B2:D6,G1,I1)

As shown in the picture below:

Explanation:
pselect()function gets the positions of the eligible members. (N) represents the Nth member; it represents members at multiple positions when N is an integer sequence. to(2,) gets members from the 2nd to the last; ~ is the current member.

Comments

[Judith-Excel-Sharing]

For anyone passionate about streamlining Excel tasks or diving into data analysis, join the conversation in our communities! 🚀
🔺Discord: discord.gg/hgbKEvJ4
🔺Reddit: reddit.com/r/esProc_Desktop/

[Judith-Excel-Sharing]

Please feel free to download esProc Desktop and explore the data processing journey on your own⬇️
🔸SPL download address: scudata.com/download-Desktop
🩵Plugin Installation Method: c.scudata.com/article/1652061135502
🔸References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
🩵SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...