Using indirection to calculate a variable cell reference in SUMIF and COUNTIF

Indirect cell reference (possibly) does not work in COUNTIFS and SUMIFS.

LibreOffice Version:, Linux Mint 20.3, Kernel 5.15.0-48-generic
(I also get the same error in “AndrOpen Office 5.2.2” which is based on Openoffice 4.1.12)

For a long time I have been building a cell reference out of fixed text and the content of a cell
to link to the next entry in an event log. That log has grown beyond the original limits and in
anticipation of future growth, I tried to get similar indirection to work in COUNTIFS and SUMIFS.
It doesn’t work, or I have got something wrong.

In the EventLog, column A contains an incrementing sequence number, column B the event date.
The cell that locates the next entry is F2, containing =MAX(EventLog.$A2:$A300)+1
The indirection is in E2, =HYPERLINK("#EventLog.$B"&$F$2+1,"Next event")

As stated, this works perfectly. However, in a Summary sheet I count (and sum) all eventlog entries that match the criteria in A28 and Constants.$B$1.


gives me Err:502. Attempting to trace the invalid argument doesn’t really help, arrows point to
cells A28 and $F$2, with a single further arrow pointing to a small diamond blob that does nothing…

however, specifying a fixed range,


works perfectly, but does not protect me from future growth…

I have even tried specifying the sheet label in every cell reference,


SUMIFS fails in exactly the same way.




does not, Err:502.

Remember, replacing the indirection with an absolute cell reference works as expected.

Is it me, do I have a syntax error somewhere?
Could it be an intentional design feature for which I cannot find documentation?
Does indirection work anywhere other than in a hyperlink?
Impossibly, could I have tripped over a bug?


[erAck: edited to format formula expressions as code for readability and correctness, see This is the guide - How to use the Ask site? - #6 by erAck ]


Thank you, I was not aware of the INDIRECT function. After some hours of repeated syntax errors, I finally have it working, using


Are there other functions that require a string reference that can usefully be modified by appending a value with ‘&’ ? (Apart from HYPERLINK)


There is no function that requires a “string reference” other than INDIRECT(). And you need INDIRECT() only if you have a text string that shall be converted to a reference.


creates a hyperlink to, for example, #EventLog.$B2 (if $F$2 contains 1).

What do you think the expression
is supposed to do? You are trying to create a range reference from EventLog.$I$2 to a concatenated string "#EventLog.$I"&Summary.$F$2, of course that does not work.

If you want to calculate cell/range references from text you need to use INDIRECT(), like


(top left direct reference, bottom right indirect reference) or


(the entire range an indirect reference).
Note also that the hyperlink’s # fragment anchor is not used in references.

creates a hyperlink to, for example, #EventLog.$B2 (if $F$2 contains 1).
Yes exactly, $F$2 contains a =MAX function to locate the largest sequence number in EventLog column A, so MAX+1 is the row withe first blank date field (column B)
My entire problem was a failure to realise that I was trying to use a string reference to a cell in a function that could not use such a string reference.
The indirection worked in HYPERLINK, so why didn’t it work elsewhere? Now I know…!


Works, but since the cells doing the =COUNTIFS and =SUMIFS are on the Summary sheet, I don’t actually need the sheet reference.
Again, Thank you to all. I have learned something new, and at my age that is not always easy!

That including the Summary sheet name was exactly taken from your own 3rd example in your question, I used it for completeness.

Granted. My third example was going to extremes trying to make sense of errors I could not understand. Why those errors occurred is now obvious, most functions cannot use a string interpretation of a cell reference, HYPERLINK and INDIRECT are among the few that do.

INDIRECT is explizitly designed for this job.
HYPERLINK NEEDS a Textreference to preventing the evaluation to the content of target instead the target-address