[Assuming XL 2002 or later....]
The sum_range can be a little misleading. The way SUMIF works is that the dimensions of range (in this case an entire column: ZTM!B:B) will determine the dimensions of sum_range. The important piece of information supplied by sum_range is the starting cell. This means that the outcome you want:
Is not strictly possible. SUMIF would conditionally sum the whole of column AA because range is the whole of ZTM!B:B.
Additionally, when you write the formula, if range and sum_range are not the same size, then the SUMIF() function becomes volatile. This means it recalculates even when a precedent cell hasn't been changed. This feature is documented here. My recommendation is that range and sum_range should always be kept the same size because it does not then imply that the function is doing something which it is not and because the function is then not volatile.
In terms of getting a cell reference from a string written into a cell: it is possible to do what you want by incorporating the INDIRECT() function into the formula. INDIRECT() is a volatile function and has a few other limitations, so it's not a very elegant solution.
Can you give us a bit more information on why you want to do this... we might be able to suggest a better approach to it?
Bookmarks