CountA() with cell reference.

[welshgasman]

Hi all,
I have a workbook, that we are going to use as an address book for the local community car scheme I volunteer for.
There are tabs for each character of the alphabet.

I want to create a Stats sheet that shows quickly how many are on each sheet.
So in the stats sheet column A, I have values A to Z by using formula =CHAR(ASC(63+ROW()))

I now want to add in column B the COUNTA() of each particular sheet, but refer to cell in column A for the sheet name.

I cannot get the concatenation correct. I have tried = & "COUNTA(" & A2 & "!A:A")-1 and a few other permutations, but just get an error message.
Is it possible to refer to a cell value like this?, and if so, what is the syntax please.?

I know I could just hard code it, as there are only 26, but the logic could come in useful in the future.

TIA

 

[nemmi69]

did you try =COUNTA(A1:A10, C1:C10)

 

[welshgasman]

To late to edit.

Whilst hardcoding for the time being, for characters C & R, Excel puts single quotes around those characters for the sheet name, but not the others? Anyone know why this would happen?

 

[welshgasman]

did you try =COUNTA(A1:A10, C1:C10)

Hi,
Thank you for the quick reply.

No, as I am trying to count for each particular sheet?
For the moment I have hard coded the references, so I have =COUNTA(A!A:A)-1 then =COUNTA(B!A:A)-1 then =COUNTA('C'!A:A)-1 etc

 

[Alex Blakenburg]

I am guessing you want something like this:

Book2
	A	B
1	Sheet	Cnt
2	Sheet2	4
3	Sheet3	2
4	Sheet4	7
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTA(INDIRECT("'"&A2&"'!A:A"))-1

 

[nemmi69]

The following formula in D8 counts the number of times a value in A2:A9, across all the sheets listed in A2:A4 of the Summary sheet, with a criteria that equals the value in C2 of the Summary sheet.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A5&"'!A2:A9"),C2))

 

[welshgasman]

I am guessing you want something like this:

Book2
	A	B
1	Sheet	Cnt
2	Sheet2	4
3	Sheet3	2
4	Sheet4	7
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTA(INDIRECT("'"&A2&"'!A:A"))-1

That would be an excellent guess, and it works brilliantly.

Thank you very much.

 

[nemmi69]

Going further

 

[welshgasman]

View attachment 80755

The following formula in D8 counts the number of times a value in A2:A9, across all the sheets listed in A2:A4 of the Summary sheet, with a criteria that equals the value in C2 of the Summary sheet.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A5&"'!A2:A9"),C2))

Thank you nemmi69,

Not quite what I was after, in fact more complicated, I just wanted a simple count of the entries, no criteria, just as Alex has posted.
However I see the same use of INDIRECT(), which I believe was my issue in the first place.

Thank you both for the prompt replies.
I am off out now to pick up a passenger.

Have a great day, you have started mine off very well.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.