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.
| A | B | C | D | |
| 1 | ||||
| 2 | Jan | Feb | Mar | |
| 3 | Sales | 10000 | 15000 | 20000 |
| 4 | COGS | 5000 | 7500 | 10000 |
| 5 | Expenses | 2000 | 2000 | 2000 |
| 6 | Net Profit | 3000 | 5500 | 8000 |
We want to sum values within the interval defined by the start month and end month parameters defined in G1 and I1.
| G | H | I | |
| Start Month | Jan | End Month | Feb |
| Sales | 25000 | ||
| COGS | 12500 | ||
| Expenses | 4000 | ||
| Net Profit | 8500 |
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.