SUMIF & INDIRECT Returns #Value!

C

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

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.
 
C

Christi

Your solution is genius. Thank you!

T. Valko said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top