Cross sheet formula - Reporting by week

Stuart.d

Hi Again! 



Is it possible to create data that provides QTY of selections checked per week and record this on a sheet automatically with formula. 



So we are managing live installation projects, with a high turnover of projects. Our main sheet is called PROJECT MILESTONES where we track all the live project activity. 



Within this sheet I have a column called "SNAGS" which is  a red flag check option and a column called "SNAG COMPLETE" which is a tick check option 



I want to be able to by WEEK- see how many active red flag SNAGS there has been and how many active SNAGS have been complete. 



My thinking is to create another data sheet, which lists the 52 weeks and date periods, which can calculate between X date and Y date how many red flag snags and how many were completed during that week period only



I will then pull this data into a visual dashboard. So by week I can see the trends if numbers are reducing or increasing. 



Problem is the rows are archived once the project is finally complete and we are paid, but I would need the data previously collected to remain in place. 



Can this be automated or is it purely going to be a manual thing where I count myself each week and type it into the fields? 





I hope I've made sense..... Any help appreciated 



Regards 

Stuart

Andrée Starå

Hi Stuart,

Problem is the rows are archived once the project is finally complete and we are paid, but I would need the data previously collected to remain in place. 



Can thisbe automated or is it purely going to be a manual thing where I count myself each week and type it into the fields? 

You could let everything stay in the main sheet and use a filter to only show the active projects.

Another way could be to copy/move the completed projects to another sheet manually or automatically with the help of third-party service like Zapier or similar.

Would any of those options work?

 

Try this. (Replace the ranges with cross-sheet references)

Add two helper columns:

WeekNumber (Will show what week the date in your date column is):

=WEEKNUMBER(DateColumn@row)    

WeekForTotals (Will be a list of all the weeks 1-52):

 

Snags Qty

=COUNTIFS(SNAGS:SNAGS; 1; WeekNumber:WeekNumber; WeekForTotals@row)    

The same version but with the below changes for your and others convenience.    

=COUNTIFS(SNAGS:SNAGS, 1, WeekNumber:WeekNumber, WeekForTotals@row)

Snags Closed

=COUNTIFS([SNAGS COMPLETE]:[SNAGS COMPLETE]; 1; WeekNumber:WeekNumber; WeekForTotals@row)    

The same version but with the below changes for your and others convenience.    

=COUNTIFS([SNAGS COMPLETE]:[SNAGS COMPLETE], 1, WeekNumber:WeekNumber, WeekForTotals@row)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting