...

Define a dynamic range of columns and perform aggregation

 

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

I 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

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)


pselect()function gets 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.
Source:https://stackoverflow.com/questions/78163830/excel-sumifs-with-dynamic-columns


 
 •  67 View  •