OK finally got my head around it.
But I'm not going to try to confuse someone reading this with a clumsy
explaination, they'll hopefully understand and interpret the solution to my
problem which was:
=SUMIF($AA:$AA,$A3,$Z:$Z)
regards,
build

"build" wrote in message
...
Ahhhhaaaaaaa
from http://www.cpearson.com/excel/named.htm "They do not depend on the
cell from which the name is invoked. " ... Hmmm If they do not depend on
the cell from which the name is invoked then is it possible to have a
reference for a LOOKUP or SUMIF?

Now I'm really confused.



"build" wrote in message
...
Damn, this is confusing me and I think it's the references
absolute/relative stuff etc?

I have a function =SUMIF(AA:AA,A:A,Z:Z) ... works well when used in for
example
=IF(SUMIF(AA:AA,A:A,Z:Z)=1,10,IF(SUMIF(AA:AA,A:A,Z :Z)=2,5,0))
but when I name it.
Qual_Pos
=SUMIF($A$A:$AA,$A:A,$Z:$Z)
it is changed to:
=SUMIF('1-Australia'!$AA:$AA,'1-Australia'!$A:$A,'1-Australia'!$Z:$Z)
when i try:
=IF(Qual_Pos=1,10,IF(Qual_Pos=2,5,0))
it does not give the expected result.
I've tried:
=SUMIF(AA:AA,A:A,Z:Z)
and other variations.
What am I missing?
Also I think I'd rather a relative reference as I'm still building the
workbook and inserting and deleting columns.

again Thanking you in anticipation,
build


"Barb Reinhardt" wrote in
message ...
Have you thought about storing them as a named range?

http://www.cpearson.com/excel/named.htm

Scroll down to the Naming Formulas section.
--
HTH,
Barb Reinhardt



"build" wrote:

G'day All,
Is it possible to use variables in formulas without storing them in a
cell?

i.e. (a simplified example)
VarA=SUMIF(A:A,Y:Y,X:X)
VarB=SUMIF(A:A,Y:Y,W:W)
IF(B:B<VarA,VarA,VarB)

looks a lot better than
IF(B:B<SUMIF(A:A,Y:Y,X:X),SUMIF(A:A,Y:Y,X:X),SUMIF (A:A,Y:Y,W:W))

The formula I have is too long and complex therefor would be a
nightmare to
trouble shoot a year down the track. Also storing the variables in a
cell
seems sloppy to me.