Using CONCAT to CountA from multiple WB's

[cbarryb]

I have a list of workbook names and the sheet names that I would like to use the counta in column B. I have used this formula to pull all of the data together so that I have a string of text that duplicates if I was to have written the formula for the individual book. i.e:

=COUNTA('[ROYS - 01.06.2021 BBC.xlsx]01.06.2021 GBP '!$B2:$B500)

I have searched and found this code to bring the CONCAT into the Function:

=SUMPRODUCT(COUNTA(INDIRECT("'["&F3&"'!$B:$B")))

The cells that I have =CONCAT, bring this text back and is added to the SUMPRODUCT formula:

ROYS - 01.06.2021 BBC.xlsx]01.06.2021 GBP​

The first cell brings back a correct amount of lines, but If I copy this down, I get the number 1 only, where there should be more than 5 and some are over 100 lines.

Can anyone see where I am going wrong please or have a simpler way of doing this maybe?

Thanks

 

[StephenCrump]

Are you accidentally adding a space when you concatenate workbook and worksheet names?

For example, is your first worksheet '01.06.2021 GBP ' as shown? Or rather '01.06.2021 GBP'?

 

[cbarryb]

Are you accidentally adding a space when you concatenate workbook and worksheet names?

For example, is your first worksheet '01.06.2021 GBP ' as shown? Or rather '01.06.2021 GBP'?

Thanks for your reply, I did check all through them and I don't have the space at the end now and I'm still not getting the result I need. I think I'm trying to over complicate this, I will keep working on this and post when I've found the answer, I'll reply