String argument to INDIRECT

Question

I have an unwieldy cell argument along the lines of:

COUNTIFS(E1:E300,"5",$O$2:$O$300,"Apple")

I would like to use INDIRECT as follows:

COUNTIFS(INDIRECT("E"&A1&":E"&A2),"5",INDIRECT("O"&A1&":O"&A2),"Apple") INDIRECT("E"&A1&":E"&A2))

Where A1 holds 1 and A2 holds 300. But again this is unwieldy, Ideally instead of A1 and A2, I would have a cell A3 containing. Similarly for A4

"E"&A1&":E"&A2

Then use

COUNTIFS(INDIRECT(A3),"5",INDIRECT(A4),"Apple") INDIRECT(A3))

Is this possible?

I can get it to work with a simple string like, but as soon as I add an & then it no longer works.

Answer 1

You don't say how 'it no longer works'.

Your logic and example works fine for me although I'm not sure what the final 'stray' INDIRECT statement is in your code lines?

If there is more than one criterion, COUNTIFS returns the count of the ANDed criteria not the sum of the counts.