Macro - Sum counted rows with specific words from several files

[srosen]

Hi,

i need to present a weekly report which would sum the results of validated values in several excel files (excel 2010).
It is not the same files every week, however all files are in exactly the same format.
The report needs to sum the number of approved, rejected and revised values in the files, however only values marked as 'Done' should be taken into account.

this is the format of the files (all files have identical format)

[TABLE="width: 288"]
<tbody>[TR]
[TD="class: xl68, width: 72, align: left"][TABLE="width: 324"]
<tbody>[TR]
[TD]ID[/TD]
[TD="align: left"]Value[/TD]
[TD="align: left"]Status[/TD]
[TD="align: left"]Done[/TD]
[/TR]
[TR]
[TD]356[/TD]
[TD="align: left"]Vienna[/TD]
[TD="align: left"]Rejected[/TD]
[TD="align: left"]Done[/TD]
[/TR]
[TR]
[TD]548[/TD]
[TD="align: left"]USA[/TD]
[TD="align: left"]Approved[/TD]
[TD="align: left"]Done[/TD]
[/TR]
[TR]
[TD]953[/TD]
[TD="align: left"]Canada[/TD]
[TD="align: left"]Approved[/TD]
[TD="align: left"]Done[/TD]
[/TR]
[TR]
[TD]852[/TD]
[TD="align: left"]Melbourne[/TD]
[TD="align: left"]Rejected[/TD]
[TD="align: left"]Done[/TD]
[/TR]
[TR]
[TD]963[/TD]
[TD="align: left"]England[/TD]
[TD="align: left"]Revised[/TD]
[TD="align: left"]Done[/TD]
[/TR]
[TR]
[TD]961[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]962[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]964[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl68, width: 72, align: left"][/TD]
[TD="class: xl68, width: 72, align: left"][/TD]
[TD="class: xl69, width: 72, align: left"][/TD]
[/TR]
</tbody>[/TABLE]

the report format is:

[TABLE="width: 898"]
<tbody>[TR]
[TD][/TD]
[TD]Approved[/TD]
[TD]Rejected[/TD]
[TD]Revised[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Sum of files 1,2,3,4[/TD]
[TD]5559[/TD]
[TD]396[/TD]
[TD]198[/TD]
[/TR]
[TR]
[TD="align: left"]Sum of files 5,6[/TD]
[TD]2181[/TD]
[TD]94[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Until now i've been using the countifs formula in every file to count the total of approved, rejected, revised values in every file, and then manually summed up the results of several files together for the report.

these are the formulas i've used:
=COUNTIFS($P:$P,"Done",$J:$J,"Rejected")
=COUNTIFS($P:$P,"Done",$J:$J,"Approved")
=COUNTIFS($P:$P,"Done",$J:$J,"Revised")

this is very time consuming, so i am looking for a macro which will automatically calculate the data,
and present the weekly report in the correct format.

Looking forwards to your reply!

 

[XLfriend777]

Hi srosen - As nobody has tried this I thought I might try to help. You might try creating a summary spreadsheet using the formulas

=COUNTIFS([test3.xlsx]Sheet1!$P:$P,"Done",[test3.xlsx]Sheet1!$J:$J,"Rejected" )

If the data comes each week in spreadsheets with standard names, or if you could rename the spreadsheets in a standard manner, such as 140108 you could set up the summary spreadsheet so that it automatically calculated the totals each week. It would take a little while to get it set up, but then each week, the summary would automatically calculate the totals when you popped the new spreadsheets in the same folder.

Hope this helps.

 

[srosen]

Hi Goesr,

Thank you very much for your reply.

i tried setting up such a sheet:
i created a folder called 'Joan - Reports', in which i placed all the individual files the formula was meant to calculate based upon.
however as soon as i closed the workbooks which the formula was based on,
and changed the files in the designated folder to updated files (with exactly the same name)
the formula result changed to 0.
in addition to that, instead of the formula containing only the references to the workbooks themselves,
after closing the original files the formula changed to showing the entire path of each workbook.

Is there any way to make this still to work?

The current showing formula (with result 0) is:
=COUNTIFS('[Discovery Work File - 1.1.xlsx]Sheet1'!$P:$P,"Done",'[Discovery Work File - 1.1.xlsx]Sheet1'!$Q:$Q,L2)+COUNTIFS('\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 1.xlsx]Sheet1'!$P:$P,"Done",'\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 1.xlsx]Sheet1'!$Q:$Q,L2)+COUNTIFS('\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 2.xlsx]Sheet1'!$P:$P,"Done",'\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 2.xlsx]Sheet1'!$Q:$Q,L2)+COUNTIFS('\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 3.xlsx]Sheet1'!$P:$P,"Done",'\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 3.xlsx]Sheet1'!$Q:$Q,L2)+COUNTIFS('\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 4.xlsx]Sheet1'!$P:$P,"Done",'\\192.168.233.230\Content Team\Discovery Engine\Joan - Reports\[Discovery Work File - 4.xlsx]Sheet1'!$Q:$Q,L2)

Many thanks for your kind help!