| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Sumif and INDIRECT | Excel Worksheet Functions | |||
| Is there something like a SUMIF that just returns the info. in a c | Excel Worksheet Functions | |||
| Using Indirect in a Sumif Function returns the wrong answer | Excel Discussion (Misc queries) | |||
| indirect returns #REF! regardless of where it is used. | Excel Worksheet Functions | |||
| Sumif range returns #NUM! | Excel Worksheet Functions | |||
Hybrid Mode
