Indirect problem

[handysmurf]

I'll perhaps get more in depth if needed but for now is there anything obviously wrong with this formula?

I'm trying to narrow down the problem by breaking the formula down into pieces. Here is what I've done and where it breaks.

"=SUM(INDIRECT($U$2&$G39):INDIRECT($U$2&$H39))" This works

"=SUMIF($R$5:$R38,"D",U5:U38)" This works

"=SUMIF($R$5:$R38,"D",INDIRECT($U$2&$G39):INDIRECT($U$2&$H39))" This is where it breaks and returns $0, not the answer I want.

It gets more complicated ultimately but this is the hurdle.

 

[Phuoc]

What is the value in U2 and G39?

 

[handysmurf]

U2 = "U"
G39 is a row number

 

[Phuoc]

Try this:

=SUMIF(INDIRECT("R"&$G39&":R"&$H39),"D",INDIRECT($U$2&$G39&":"&$U$2&$H39))

 

[handysmurf]

Um no that returned a number way off

Im trying to figure out how to upload the worksheet thing.... ?

 

[handysmurf]

wait that did work.... autorecover hit and it opened up a workbook where I had added two colums. Once I deleted those it works.

Thanks so much.... hopefully i can manage to parse that for other cells ... projects,... issues

 

[handysmurf]

So apparently it doesn't like to mix direct cell references and indirect cell references in the same formula?

 

[Alex Blakenburg]

So apparently it doesn't like to mix direct cell references and indirect cell references in the same formula?

That unlikely to have been the real issue.
The real issue is that each range has to match in term of starting and finishing at the same row
You have hard coded one range as being rows 5:38.
Having any other range as variable (aka indirect) doesn't then make sense since the formula requires the other range to also be rows 5:38

 

[RoryA]

the formula requires the other range to also be rows 5:38

It doesn't actually. The ranges need to be the same shape/size, but they don't need to be related in any other way. You could have criteria in rows 2 and 3 and values in rows 4 and 5 and it would still work. (it would be a weird spreadsheet though)

 

[Alex Blakenburg]

It doesn't actually. The ranges need to be the same shape/size, but they don't need to be related in any other way. You could have criteria in rows 2 and 3 and values in rows 4 and 5 and it would still work. (it would be a weird spreadsheet though)

I did not know that. I don't think it qualifies as TKO though .
As you said it would be a weird spreadsheet that had the criteria range and sum range the same size but out of alignment.

@handysmurf - to recap having one of the ranges being variable while the other is static doesn't make sense since you want them to line up ie be in sync.