SUMIF & INDIRECT Returns #Value!

[Christi]

In the formula below, the reference 'Activity Metrics'!$J$6:$J$143 will shift
one column each month:
SUMIF('Activity Metrics'!$B$6:$B$143,'Team Leader Cross Check'!G$8,'Activity
Metrics'!$J$6:$J$143) Returns 98

I'm trying to automate this with the following formula:
=SUMIF('Activity Metrics'!$B$6:$B$143,'Team Leader Cross
Check'!G$8,INDIRECT(ADDRESS(6,6+MONTH(Index!$C$3),1,TRUE,"Activity
Metrics")):INDIRECT(ADDRESS(143,6+MONTH(Index!$C$3)))) Returns #Value!

formula cross-checks:
=MONTH(Index!$C$3) Returns 4
=ADDRESS(6,6+MONTH(Index!$C$3),1,TRUE,"Activity Metrics") Returns 'Activity
Metrics'!$J$6

What have I done wrong? Any help is greatly appreciated.

 

[T. Valko]

Your INDIRECT formula works ok for me.

You don't need to build the range that way. You can do something like this:

=SUMIF('Activity Metrics'!$B$6:$B$143,'Team Leader Cross
Check'!G$8,INDEX('Activity Metrics$C$6:$Z$143,,6+MONTH(Index!$C$3)))

You haven't provided enough info to be more specific but you'd need to
adjust for the correct range and adjust for the correct offset where you're
doing 6+MONTH.

Maybe you have a column header that defines which column is to be used as
the sum_range? It's be better to match that column header.

 

[T. Valko]

Your INDIRECT formula works ok for me.

You don't need to build the range that way. You can do something like this:

=SUMIF('Activity Metrics'!$B$6:$B$143,'Team Leader Cross
Check'!G$8,INDEX('Activity Metrics$C$6:$Z$143,,6+MONTH(Index!$C$3)))

You haven't provided enough info to be more specific but you'd need to
adjust for the correct range and adjust for the correct offset where you're
doing 6+MONTH.

Maybe you have a column header that defines which column is to be used as
the sum_range? It's be better to match that column header.

 

[Christi]

Your solution is genius. Thank you!

Your INDIRECT formula works ok for me.

You don't need to build the range that way. You can do something like this:

=SUMIF('Activity Metrics'!$B$6:$B$143,'Team Leader Cross
Check'!G$8,INDEX('Activity Metrics$C$6:$Z$143,,6+MONTH(Index!$C$3)))

You haven't provided enough info to be more specific but you'd need to
adjust for the correct range and adjust for the correct offset where you're
doing 6+MONTH.

Maybe you have a column header that defines which column is to be used as
the sum_range? It's be better to match that column header.

 

[T. Valko]

You're welcome. Thanks for the feedback!