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

English
#1

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

LibreOffice Version: 6.4.7.2, 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.

=COUNTIFS(EventLog.$I$2:"#$I"&$F$2,A28,EventLog.$D$2:"#D"&$F$2,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,

=COUNTIFS(EventLog.$I$2:I$300,A28,EventLog.$D$2:D$300,Constants.$B$1)

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

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

=COUNTIFS(EventLog.$I$2:"#EventLog.$I"&Summary.$F$2,A28,EventLog.$D$2:"#EventLog.$D"&Summary.$F$2,Constants.$B$1)

SUMIFS fails in exactly the same way.

=SUMIFS(EventLog.$E$2:$E$300,EventLog.$I$2:I$300,A28,EventLog.$D$2:D$300,Constants.$B$1)

works,

=SUMIFS(EventLog.$E$2:"#$E"&$F$2,"#EventLog.$I$2:I"&$F$2,A28,EventLog.$D$2:"#D"&$F$2,Constants.$B$1)

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?

JonT

[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 ]

#2

INDIRECT?

#4

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

=COUNTIFS(EventLog.$I$2:INDIRECT("EventLog.$I"&$F$2),A28,EventLog.$D$2:INDIRECT("EventLog.$D"&$F$2),Constants.$B$1)

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

JonT

#7

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.

#3

This

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

What do you think the expression
EventLog.$I$2:"#EventLog.$I"&Summary.$F$2
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

EventLog.$I$2:INDIRECT("EventLog.$I"&Summary.$F$2)

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

INDIRECT("EventLog.$I$2:$I"&Summary.$F$2)

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

#5

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…!

EventLog.$I$2:INDIRECT("EventLog.$I"&Summary.$F$2)

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!
JonT

#6

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

#8

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.

#9

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.