SUM, IF using defined names and INDIRECT

[HUPAXE]

Hello Guys,

I got probably a simple problem but I am unable to understand why I can't get my solution to work.

Here's the thing that works.
{=SUM(IF(Query1_results!F19273:F26982="US", Query1_results!G19273:G26982, FALSE))}

This results in getting the answer what I expect.

However, the row numbers and the sheet tab are hard coded in the above formula. If I use the formulas that determine the sheet tab and the column and row numbers then the formula is complicated and it doesn't work either. The answer I get is #Value!. Please tell me what I am doing wrong.

{=SUM(IF(INDIRECT(STab&"!"&Region_Col_Que1&Start_Row_2011_Que1&":"&Region_Col_Que1&End_Row_2011_Que1)=US, INDIRECT(STab&"!"&Unit_Col_Act_Que1&Start_Row_2011_Que1&":"&Unit_Col_Act_Que1&End_Row_2011_Que1), FALSE))}

STab is the name defined to give me Query1_results.
Region_Col_Que1 is defined to give me F.
Start_Row_2011_Que1 is defined to give me 19273.
End_Row_2011_Que1 is defined to give me 26982.
Unit_Col_Act_Que1 is defined to give me G.

Thanks,

Hupaxe

 

[jasonb75]

Try each of the following in a cell

=Start_Row_2011_Que1
=End_Row_2011_Que1
=Curr_Ending_Period

The only possibility I can see is that the error is coming from one of those named ranges, this will help to find which.

 

[HUPAXE]

Start_Row_2011_Que1 gives me 19273
End_Row_2011_Que1 gives me 26982
Curr_Ending_Period gives me June 2011.

 

[HUPAXE]

Jason,

The problem seems to be solved for now. Thanks very much to you.
The index formula is more efficient.
Also realized that for some reason the index formula was using double quotes which made the SUMIF treat it like a text. After I removed the double quotes it's giving me a good number.
I'll test these for variety of cases and hope it will all workout. \\


Thanks,

Hup