Sumproduct when data column changes

[navin]

Hello All,

In my excel sheet, i need to apply sumproduct formula but the problem
is that the column which contains the data changes every month. Eg. if
in Jan, Column A contains the data then next month it will shift to
column B.

To do this, in a sheet (same workbook), i entered the column names,
which contains the data and tried refrencing them with indirect
formula.

A1=B (Column which contains the data to check)
A2=120 (total rows)
A3=Sheet1 (Sheet which contains the data)
A4=C (Column which contains data to sum)

Below is the formula which i am using:

=SUMPRODUCT(($A$3&"!$"&INDIRECT(CELL("address",$A$1))&"$2:$"&INDIRECT
(CELL("address",$B$1))&"$"& $A$2="INCLUDE"))*$A$3&"!$"&INDIRECT(CELL
("address",$A$4))&"$2:$"&INDIRECT(CELL("address",$A$4))&"$"&$A$2)

above formula checks the first condition but when it comes to summing
up the column it gives #value error.

Please help to get this formula working. If there are any suggestions
to do this in a different way, please let me know.

Thanks in advance for help.

Navin

 

[David Heaton]

Hello All,

In my excel sheet, i need to apply sumproduct formula but the problem
is that the column which contains the data changes every month. Eg. if
in Jan, Column A contains the data then next month it will shift to
column B.

To do this, in a sheet (same workbook), i entered the column names,
which contains the data and tried refrencing them with indirect
formula.

A1=B (Column which contains the data to check)
A2=120 (total rows)
A3=Sheet1 (Sheet which contains the data)
A4=C (Column which contains data to sum)

Below is the formula which i am using:

=SUMPRODUCT(($A$3&"!$"&INDIRECT(CELL("address",$A$1))&"$2:$"&INDIRECT
(CELL("address",$B$1))&"$"& $A$2="INCLUDE"))*$A$3&"!$"&INDIRECT(CELL
("address",$A$4))&"$2:$"&INDIRECT(CELL("address",$A$4))&"$"&$A$2)

above formula checks the first condition but when it comes to summing
up the column it gives #value error.

Please help to get this formula working. If there are any suggestions
to do this in a different way, please let me know.

Thanks in advance for help.

Navin

Navin,

It would help to know more how your data sheet is organised as there
may be better ways than this, but in the interim try this

In a5 = CONCATENATE(A3,"!",A1,"2:",A1,A2)
in a6 =CONCATENATE(A3,"!",A4,"2:",A4,A2)

then your SUMPRODUCT formula becomes
=SUMPRODUCT((INDIRECT(A5)="INCLUDE")*(INDIRECT(A6)))

hth

regards

David