Creating a Task Completion Summary sheet using formulas from a copy/paste dump

[mradrenaline]

	A	B	C	D	E	F	G	H	I
1		Monday	Tuesday	Wednesday	Thursday	-	Completed	Goal	Percent
2	Leader	1	1	0	0	-	4	4	100%
3	Boss1	1	0	0	0	-	1	4	25%
4	Boss2	0	0	0	0	-	0	4	0%
5
	Sheet 1

<tbody>
</tbody>

	A	B	C	D	E	F	G
1	N/A	N/A	Submit Date	N/A	N/A	N/A	Owner
2	N/A	N/A	6-Nov-17	N/A	N/A	N/A	Leader
3	N/A	N/A	6-Nov-17	N/A	N/A	N/A	Leader
4	N/A	N/A	6-Nov-17	N/A	N/A	N/A	Leader
5	N/A	N/A	6-Nov-17	N/A	N/A	N/A	Boss1
6	N/A	N/A	7-Nov-17	N/A	N/A	N/A	Leader
	Sheet 2

<tbody>
</tbody>

Sheet 2 is how information populates if I copy/paste it into excel from the internet. Sheet 1 is the desired format for the Task Completion Summary sheet. I need the formulas to get this information from the copy/paste format into sheet 1 with the shown format. I'm able to set up the conditional formatting, but I don't know how to get excel to recognize the day of the week from the date shown in sheet 2 and auto-count it for each person into sheet 1 based on the day of the week. I need the information from sheet 2 to be translated to the summary report from sheet 1 using formulas. Any help is much appreciated.

 

[Truiz]

I think this can be accomplished like this

Excel 2012

	A	B	C	D	E	F	G	H	I	J
1		Monday	Tuesday	Wednesday	Thursday	Friday	-	Completed	Goal	Percent
2	Leader	3	1	0	0	0	-	4	4	100%
3	Boss1	1	0	0	0	0	-	1	4	25%
4	Boss2	0	0	0	0	0	-	0	4	0%

<tbody>
</tbody>

Hoja3​

Worksheet Formulas Cell Formula B2 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2) C2 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2) D2 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2) E2 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2) F2 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$2) B3 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3) C3 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3) D3 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3) E3 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3) F3 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$3) B4 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4) C4 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4) D4 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4) E4 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4) F4 =COUNTIFS(Hoja4!$D$2:$D$6,COLUMN(),Hoja4!$H$2:$H$6,Hoja3!$A$4) H2 =SUM(B2:F2) J2 =H2/I2 J3 =H3/I3 J4 =H4/I4 <tbody> </tbody>

<tbody>
</tbody>

As you can see I added a helper column in the data and used a formula to get a value for the day in the date


Excel 2012

	A	B	C	D	E	F	G	H
1	N/A	N/A	Submit Date		N/A	N/A	N/A	Owner
2	N/A	N/A	6-Nov-17	2	N/A	N/A	N/A	Leader
3	N/A	N/A	6-Nov-17	2	N/A	N/A	N/A	Leader
4	N/A	N/A	6-Nov-17	2	N/A	N/A	N/A	Leader
5	N/A	N/A	6-Nov-17	2	N/A	N/A	N/A	Boss1
6	N/A	N/A	7-Nov-17	3	N/A	N/A	N/A	Leader

<tbody>
</tbody>

Hoja4​

Worksheet Formulas Cell Formula D2 =WEEKDAY(C2) D3 =WEEKDAY(C3) D4 =WEEKDAY(C4) D5 =WEEKDAY(C5) D6 =WEEKDAY(C6) <tbody> </tbody>

<tbody>
</tbody>

Sunday = 1
Monday = 2
Tuesday = 3

and so on