Dynamic column range based on dropdown menu

[DravenExcel]

Hi there,

I'm looking for help with a problem in regards to using a dropdown menu for a dynamic summation of periods. I have a drop down menu with P1-P12 in it.

Here is the report that I have to use for the information.

Right now I have been working on by just using SumProduct to get the required totals I need but columns based on the expense numbers. I've tried using Index/match to make the column range dynamic but no luck.

Expense number

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

1111​

8,515.41​

11,353.88​

5,676.94​

0​

0​

0​

0​

0​

0​

0​

0​

0​

2222​

6,917.78​

9,223.70​

11,529.63​

9,223.70​

11,529.63​

9,223.70​

9,223.70​

11,529.63​

9,223.70​

9,223.70​

11,529.63​

12,452.00​

3333​

9,527.37​

12,703.16​

15,878.95​

12,703.16​

15,878.95​

12,703.16​

12,703.16​

15,878.95​

12,703.16​

12,703.16​

15,878.95​

17,149.26​

4444​

0​

2,826.64​

4,830.15​

3,864.12​

4,830.15​

966.03​

0​

0​

0​

0​

0​

0​

5555​

0​

0​

0​

0​

2,708.55​

902.85​

0​

0​

0​

0​

0​

0​

6666​

0​

0​

0​

0​

4,533.69​

6,044.93​

6,044.93​

7,556.16​

6,044.93​

6,044.93​

0​

0​

7777​

0​

0​

0​

0​

2,406.78​

4,813.55​

4,813.55​

4,813.55​

0​

0​

0​

0​

5555​

0​

0​

0​

0​

0​

965.29​

1,287.05​

1,608.81​

1,287.05​

1,287.05​

1,608.81​

1,737.52​

4444​

0​

0​

0​

0​

0​

1,206.61​

1,608.81​

2,011.01​

1,608.81​

1,608.81​

2,011.01​

2,171.90​

8888​

0​

0​

0​

0​

0​

0​

0​

0​

0​

2,174.45​

5,436.12​

5,871.01​

6666​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

7,556.16​

8,160.65​

My output is as shown below and the formula is =SUMPRODUCT((A15:A25=A29)*B15:M25)

1111​	25546.23​
2222​	120830.5​
3333​	166411.4​
4444​	29544.05​
5555​	13392.98​
6666​	51986.38​
7777​	16847.43​
8888​	13481.58​

As soon as I add in another array to act as the drop down menu the formula stops added and only gives the first number.

Anyone able to help fix this?

Thanks in advance.

 

[alansidman]

Having difficulty using the data provided. Will you upload the same using XL2BB so that the data can be manipulated easily.

 

[Fluff]

What cell is the dropdown in & what do you want to add?

 

[DravenExcel]

Here is the data in XL2BB format. Sorry for the late reply.

Expense example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Expense number	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12
2	1111	8,515.41	11,353.88	5,676.94	0	0	0	0	0	0	0	0	0
3	2222	6,917.78	9,223.70	11,529.63	9,223.70	11,529.63	9,223.70	9,223.70	11,529.63	9,223.70	9,223.70	11,529.63	12,452.00
4	3333	9,527.37	12,703.16	15,878.95	12,703.16	15,878.95	12,703.16	12,703.16	15,878.95	12,703.16	12,703.16	15,878.95	17,149.26
5	4444	0	2,826.64	4,830.15	3,864.12	4,830.15	966.03	0	0	0	0	0	0
6	5555	0	0	0	0	2,708.55	902.85	0	0	0	0	0	0
7	6666	0	0	0	0	4,533.69	6,044.93	6,044.93	7,556.16	6,044.93	6,044.93	0	0
8	7777	0	0	0	0	2,406.78	4,813.55	4,813.55	4,813.55	0	0	0	0
9	5555	0	0	0	0	0	965.29	1,287.05	1,608.81	1,287.05	1,287.05	1,608.81	1,737.52
10	4444	0	0	0	0	0	1,206.61	1,608.81	2,011.01	1,608.81	1,608.81	2,011.01	2,171.90
11	8888	0	0	0	0	0	0	0	0	0	2,174.45	5,436.12	5,871.01
12	6666	0	0	0	0	0	0	0	0	0	0	7,556.16	8,160.65
13
14
15			Total Expenses	Check number P1-P6							Dropdown
16		1111		25,546.23		Starting Period		Ending Period			P1
17		2222		57,648.14		P1		P6			P2
18		3333		79,394.75							P3
19		4444		18,523.70							P4
20		5555		4,576.69							P5
21		6666		10,578.62							P6
22		7777		7,220.33							P7
23		8888		0.00							P8
24											P9
25											P10
26											P11
27											P12
Sheet1

Cell Formulas
Range	Formula
D22,D16:D18	D16	=SUM(B2:G2)
D19,D21	D19	=SUM(B5:G5)+SUM(B10:G10)
D20	D20	=SUM(B6:G6)+SUM(B9:G9)
D23	D23	=SUM(B11:G11)

Cells with Data Validation
Cell	Allow	Criteria
H17	List	=$K$16:$K$27
F17	List	=$K$16:$K$27

 

[Fluff]

How about

=SUMPRODUCT(($A$2:$A$12=B16)*(MID($B$1:$M$1,2,2)+0>=MID($F$17,2,2)+0)*(MID($B$1:$M$1,2,2)+0<=MID($H$17,2,2)+0)*($B$2:$M$12))

 

[DravenExcel]

How about

=SUMPRODUCT(($A$2:$A$12=B16)*(MID($B$1:$M$1,2,2)+0>=MID($F$17,2,2)+0)*(MID($B$1:$M$1,2,2)+0<=MID($H$17,2,2)+0)*($B$2:$M$12))

Brilliant! I was trying to do an index/match version of this and I never thought of just using mid. Thanks for your help I really appreciate it!

 

[Fluff]

You're welcome & thanks for the feedback.