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

 

[HUPAXE]

BTW, I forgot to put the "US" in the second formula. But even with the apostrophes the formula still doesn't work.

 

[jasonb75]

Welcome to the board!

Is each defined name referring to a single cell?

I haven't tried your exact ranges and names, but in principle the format works.

 

[HUPAXE]

Jason,

Thanks for looking at this post.

The various defined names are as below.

STab is defined as =MID(CELL("Filename",Query1_results!IL8),FIND("]",CELL("Filename",Query1_results!IL8))+1,255)

Region_Col_Que1 is defined as =SUBSTITUTE(ADDRESS(1,COLUMN(Query1_results!$F$1),4),"1","")

Start_Row_2011_Que1 is defined as =MATCH("January 2011",Query1_results!$A:$A,0)

End_Row_2011_Que1 is defined as =MATCH(Curr_Ending_Period,Query1_results!$A:$A,0)-1

where Curr_Ending_Period is =TEXT((MONTH(TODAY()))*29,"mmmm")&" "&YEAR(TODAY())

Unit_Col_Act_Que1 is defined as =SUBSTITUTE(ADDRESS(1,COLUMN(Query1_results!$G$1),4),"1","")

So you could see how exactly each section is defined. I just wanted to keep the formula short coz it's really for a friend of mine I am doing a favor and she commented that the formula was too big.

Thanks,

 

[jasonb75]

I'm failing to see the point in some sections of that,

=MID(CELL("Filename",Query1_results!IL8),FIND("]",CELL("Filename",Query1_results!IL8))+1,255) will always return the same sheetname.

=SUBSTITUTE(ADDRESS(1,COLUMN(Query1_results!$F$1),4),"1","") will always return F

=SUBSTITUTE(ADDRESS(1,COLUMN(Query1_results!$G$1),4),"1","") wll always return G

There are simpler ways to do that, but I don't think that is the source of the error.

What does =MATCH("January 2011",Query1_results!$A:$A,0) return when entered into a cell?

 

[HUPAXE]

I am pretty sure my problem could be solved effectively using Pivot table but the thing is that it's been a long time since I used Pivot table and it would take a lot more time for me to figure the problem out using Pivot table.

However, if somebody can help me resolve the formula using the method described then I would be quite thankful.

 

[HUPAXE]

Jason,

=MATCH("January 2011",Query1_results!$A:$A,0) returns 19273 which gives me the starting row number for the analysis.

Thanks,

 

[jasonb75]

Delete the named ranges for STab, Reg_Col and Unit_Col, keep the rest.

Create a new name - Q1R

Refers to =INDEX(Query1_results!$F:$F,Start_Row_2011_Que1):INDEX(Query1_results!$G:$G,End_Row_2011_Que1)

Your worksheet formula (non-array)

=SUMIF(INDEX(Q1R,0,1),"US",INDEX(Q1R,0,2))

 

[jasonb75]

Looking over it again, I think that will still give the same error, everything points to an error in the source data, but this should cause the hard coded version to fail too.

Try formula evaluation to see where the error first appears.

 

[HUPAXE]

Jason,
You are right. The formula with the index function also gives me the #Value!. BTW, like I said in my first post, the hard coded formula does work and gives me a value. I am still checking what's wrong !!

Ritesh