Is it possible to search specific term in mutiple excel files and count the number of records easily?

I have 10 Excel files(WorkBooks) with same header but different data. I want to search RLL in the Name column of all the files and need to count of all the 10 records after the filter.

This is a big task as I have to open each and every file and search RLL and then count and note it in a notepad/new excel sheet and doing it for remaining 9 is really frustrating.

So Is there any easy method inside Excel itself to do this task easily?

Assume u have 10 files D:\file1.xlsx to D:\file10.xlsx, and the column interested is A:A.

1. Open file1.xlsx.
2. create a new file, do:

in cell A1 put `=COUNTIF(INDIRECT("[file"&C1&".xlsx]Sheet1!$A:$A"),"RLL")`
in cell C1 put `=ROW()`

drag both downwards until row 10.

Then see the results.. only count in A1 should be available, and others should be should be #REF

To get the other value, just open the other file2-file10 at once the result will be there in A2:A10. Save it. and close all excel at once.

Idea : Use countif() to count. and play around with indirect() AND the its filename/list to 'point' to data source.

Please share if it works/understandable/not.

I like @p.phidot's approach, but it relies on the file names to be consistent. Another approach is to use Power Query to combine all files in the same folder regardless of file name. It should work well, since you said you have the same columns in each file.

Here's an example with three files in a folder. Each has a different name but similar data, all in Column A, with varying occurrences of "RLL".

Here's what's in one of the files.

In a new file, go to the Data tab on the ribbon, and choose Get Data --> From File --> From Folder. Then select your folder.

On the Power Query popup, select Combine --> Combine and Load.

Select the columns you want to search and click "OK".

Then you can use a COUNTIF (or whatever suits you) to count the occurrences of "RLL".

This is a quick and easy way to get what you need and demonstrate the concept, but play with the other options too. Power Query is great for gathering data from lots of similar spreadsheets from a folder. Any time you add more spreadsheets, you just have to hit "Refresh" and it'll update.