Sum of subtotal cell from multiple workbooks

Copper Contributor

Hi Folks,

 

I do the invoicing for my wife’s physio business. I downloaded a simple invoice template and have been using this for the past few years.

 

On OneDrive, I have a directory with my wife’s business name and then sub-directories of years and within each year, months. So, for instance, a directory 2022, with all the months of the year Jan-Dec, and within each month a patient’s invoice for that month.

 

Each workbook contains a worksheet invoice with a cell in each worksheet containing the sum of the number of visits for that month in $. What I would like to do is add up all the subtotals for each patient for each month and a total for the year.

 

For example, 2022 would have directories of Jan to Dec, with a workbook containing an invoice worksheet for each patient. I would like to get a total of all the patients’ subtotals included in each monthly directory, and then a grand total for the year. So essentially, I am trying to sum one cell from one worksheet found in multiple workbooks.

 

I am not a power user so the easier the better even if it may not be the most efficient method. I just do not want to have to open up every workbook containing the invoice and manually add the subtotal of each patient’s charges.

 

Can this be done through a power query or macro. I am not very good with Excel but I am willing to give it a try.

 

Thanks!!

 

Best regards,

Rich

2 Replies

@trickydicky7737 

Power Query shall work if all workbooks have exactly the same structure. 

Great thanks Sergei...I will try to figure this out from online resources. @Sergei Baklan