COUNTA Formula and INSERT Function Help!

[dictatordane]

Hi everyone,

I am having trouble with my formula. I want to count non-blank cells using the COUNTA function.

I want to count non-blank cells in a column in a different workbook.

The workbook I want to count from is named "File1.csv" and it is open.

If I type in the following formula it works:

=COUNTA(File1.csv!$A:$A)

It counts all the non-blank cells in column A of workbook File1.csv

I need to do this for lots of workbooks. I have the names of the workbooks in cells in the workbook I am using. For example: "File1.csv" is located in A2

When I try the following formula it does not work:

=COUNTA(a2!$A:$A)

Please help!

 

[VoG]

Hi. Try

=COUNTA(INDIRECT("'"&A2&"'!$A:$A")

 

[dictatordane]

Thank you for your prompt reply. Worked a treat!

How about if the cell does not contain the complete file name? Instead of "File1.csv" it says "File1"... Can I change the formula so it ads .csv?

Thanks

Dane

 

[VoG]

Try

=COUNTA(INDIRECT("'"&A2&".csv'!$A:$A")

 

[dictatordane]

Thanks mate - I should have been able to figure that 1 out!

 

[dictatordane]

OK I have a tricky one for you:

Problem 1

I have a database with a long list of values. I need a list of values with duplicates remove and a count of how many times each value appears in the list.

Example List:
dog
dog
cat
mouse
cat
mouse
bird
bird
bird

Output would be
dog = 2
cat = 2
mouse = 2
bird = 3

The list I have is huge so can't count manually.

Problem 2
The list I have is over 1,000,000 lines so it is in multiple workbooks. I have a column with all file names (like the formula you did before) and the column with values I need to count is the same in each workbook = R

Would be great if I could somehow do the above pulling data from column R from all workbooks listed in column A.

Probably going to take some processing power but I do have an i7 8Gb Ram Windows 7 x64 and Excel x64 so it should cope.

If you can help me out with Problem 1 that would be great. Problem 2 would be FANTASTIC!

Thanks

Dane

 

[VoG]

I suggest that you start a new thread on this different subject.