DravenExcel
New Member
- Joined
- Feb 18, 2020
- Messages
- 6
- Office Version
- 2016
- Platform
- Windows
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!
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!