SumProduct with dynamic column range not calculating properly

[DravenExcel]

Hi everyone,

First time posting so bare with me.

I'm trying use sumproduct to total up the numbers in a data table within a dynamic column range based on a cell value that corresponds to the header titles.

These titles are default titles from a report that for simplicity can't be changed. The table name is Tab_Test. and cell J6 is a drop down menu to toggle between the different Periods in the report.

My current formula is =SUMPRODUCT((Tab_Test[[P1]:[P12]])*(Tab_Test[Department]=I9)*(Tab_Test[[#Headers],[P1]:[P12]]>="P1")*(Tab_Test[[#Headers],[P1]:[P12]]<=$J$6))

The problem is that the initial "P1" in the formula is picking up the P1 from P10, P11, P12 and adding them to the totals.

Is there a way to adjust the formula so that it still picks up the range but only looks for the exact number/value?

Any help would be appreciated!

 

[Marcelo Branco]

Try changing:
the headers to P01, P02, ....,P09,P10,P11,P12
J6 to P09

and try this formula
=SUMPRODUCT((Tab_Test3[[P01]:[P12]])*(Tab_Test3[Department]=I9)*(Tab_Test3[[#Headers],[P01]:[P12]]>="P01")*(Tab_Test3[[#Headers],[P01]:[P12]]<=$J$6))

M.

 

[DravenExcel]

Their default titles off of a pre-set report.

I'm developing this template for people who aren't very knowledgeable in Excel. I'm trying to set it us so they have to just insert the report and the information they need is then generated for them. =/

 

[Marcelo Branco]

Their default titles off of a pre-set report.

I'm developing this template for people who aren't very knowledgeable in Excel. I'm trying to set it us so they have to just insert the report and the information they need is then generated for them. =/

Maybe something like this

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Department	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12
2	Marketing	1	1	1	1	1	1	1	1	1	1	1	1
3	R&D	1	1	1	1	1	1	1	1	1	1	1	1
4	Marketing	1	1	1	1	1	1	1	1	1	1	1	1
5
6									Start	P1
7									Up to period	P9
8
9										Total
10									Marketing	18
11									R&D	9
Plan1

Cell Formulas
Range	Formula
J10:J11	J10	=SUMPRODUCT((Tab_Test[[P1]:[P12]])*(Tab_Test[Department]=I10)*(COLUMN(Tab_Test[[#Headers],[P1]:[P12]])-COLUMN(Tab_Test[[#Headers],[P1]])+1>=MATCH(J$6,Tab_Test[[#Headers],[P1]:[P12]]))*(COLUMN(Tab_Test[[#Headers],[P1]:[P12]])-COLUMN(Tab_Test[[#Headers],[P1]])+1<=MATCH(J$7,Tab_Test[[#Headers],[P1]:[P12]],0)))

M.

 

[DravenExcel]

Thanks!

I would of never have been able to figure this out.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.