Excel Indirect with Count

Question

I have a workbook with different sheets for each month. I made another sheet where I put the months in A1 down. I wanted to use Counta (for A:A in the months' sheets) and indirect together but I am getting the result "1" with my formula.

Please help:

=COUNTA(INDIRECT(SheetNames!A1&"!A:A"))

I would also eventually like to do the same for countif. If you can please explain what I missed that will help me.

Thanks!

Answer 1

Trying out your formula it works perfectly unless you've got a space in the worksheet name. Try this formula:
=COUNTA(INDIRECT("'" & SheetNames!A1 & "'!A:A"))

When a sheet name contains a space then the sheet name must be enclosed within apostrophes. So =COUNTA('Sheet 4'!A:A) is a valid formula, while =COUNTA(Sheet 4!A:A) will not work and causes all kinds of problems.
The INDIRECT formula needs a valid reference as a string (rather than an actual reference) to calculate - so "'" & SheetNames!A1 & "'!A:A" returns the string 'Sheet 4'!A:A.