SUMIF & INDIRECT Returns #Value!
 

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.

SUMIF & INDIRECT Returns #Value!
 

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.

--
Biff
Microsoft Excel MVP


"Christi" wrote in message
...
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.

SUMIF & INDIRECT Returns #Value!
 

Your solution is genius. Thank you!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Christi" wrote in message
...
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.

SUMIF & INDIRECT Returns #Value!
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Christi" wrote in message
...
Your solution is genius. Thank you!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Christi" wrote in message
...
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.