Counting Unique Dates Based on Criteria

[gbc123]

Hi Folks,

Struggling with this, even having read threads here about functions that may help.

Looking to total unique dates based on criteria in another cell (not an overall total of unique dates).

e.g.
A B C D E F G
Resource Name Position Hours Date Day Day Type
1234567 Mrs Munro 100156 0 30/03/2018 School Holiday
1234567 Mrs Munro 101453 0 30/03/2018 School Holiday
1234567 Mrs Munro 100156 0 31/03/2018 School Holiday
1234567 Mrs Munro 101453 0 31/03/2018 School Holiday
1234567 Mrs Munro 100156 0 01/04/2018 School Holiday
1234567 Mrs Munro 101453 0 01/04/2018 School Holiday
1234567 Mrs Munro 100156 0 02/04/2018 School Closure Day
1234567 Mrs Munro 101453 0 02/04/2018 School Closure Day
1234567 Mrs Munro 100156 0 03/04/2018 School Closure Day
1234567 Mrs Munro 101453 0 03/04/2018 School Closure Day
1234567 Mrs Munro 100156 6 04/04/2018 Wednesday Working Day
1234567 Mrs Munro 101453 0 04/04/2018 School Closure Day
1234567 Mrs Munro 100156 2 05/04/2018 Thursday Working Day
1234567 Mrs Munro 101453 3.5 05/04/2018 Thursday Working Day
1234567 Mrs Munro 100156 0 06/04/2018 School Closure Day
1234567 Mrs Munro 101453 0 06/04/2018 School Closure Day

Would like output to be

Distinct count of dates worked where "Day Type" containts "Working Day" with a sum of hours e.g.

Day Days Worked Hours Worked
Wednesday 1 6
Thursday 1 5.5

I do not want the Thursday counted twice.

Would really appreciate assistance!

Thanks.

Gary.

 

[Eric W]

I'm still a little unclear about how you want the results, but let's try this first and see what you think:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Resource	Name	Position	Hours	Date	Day	Day Type				Resource	Day Type		Date	Day	Hours
2	1234567	Mrs Munro	100156	0	3/30/2018		School Holiday				1234567	Working Day		4/4/2018	Wednesday	6
3	1234567	Mrs Munro	101453	0	3/30/2018		School Holiday							4/5/2018	Thursday	5.5
4	1234567	Mrs Munro	100156	0	3/31/2018		School Holiday									0
5	1234567	Mrs Munro	101453	0	3/31/2018		School Holiday									0
6	1234567	Mrs Munro	100156	0	4/1/2018		School Holiday									0
7	1234567	Mrs Munro	101453	0	4/1/2018		School Holiday									0
8	1234567	Mrs Munro	100156	0	4/2/2018		School Closure Day
9	1234567	Mrs Munro	101453	0	4/2/2018		School Closure Day
10	1234567	Mrs Munro	100156	0	4/3/2018		School Closure Day
11	1234567	Mrs Munro	101453	0	4/3/2018		School Closure Day
12	1234567	Mrs Munro	100156	6	4/4/2018	Wednesday	Working Day
13	1234567	Mrs Munro	101453	0	4/4/2018		School Closure Day
14	1234567	Mrs Munro	100156	2	4/5/2018	Thursday	Working Day
15	1234567	Mrs Munro	101453	3.5	4/5/2018	Thursday	Working Day
16	1234567	Mrs Munro	100156	0	4/6/2018		School Closure Day
17	1234567	Mrs Munro	101453	0	4/6/2018		School Closure Day

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula O2 =TEXT(N2,"dddd") P2 =SUMIFS(D:D,A:A,$K$2,E:E,N2,G:G,$L$2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula N2 {=IFERROR(SMALL(IF($G$2:$G$17=$L$2,IF(COUNTIF($N$1:$N1,$E$2:$E$17)=0,IF($A$2:$A$17=$K$2,$E$2:$E$17))),ROWS($N$2:$N2)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[gbc123]

Thanks very much Eric. All good