Counting Unique Dates Based on Criteria

G

gbc123

New Member
Joined
May 6, 2004
Messages
30
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I'm still a little unclear about how you want the results, but let's try this first and see what you think:

ABCDEFGHIJKLMNOP
ResourceNamePositionHoursDateDayDay TypeResourceDay TypeDateDayHours
Mrs MunroSchool Holiday Working DayWednesday
Mrs MunroSchool Holiday Thursday
Mrs MunroSchool Holiday
Mrs MunroSchool Holiday
Mrs MunroSchool Holiday
Mrs MunroSchool Holiday
Mrs MunroSchool Closure Day
Mrs MunroSchool Closure Day
Mrs MunroSchool Closure Day
Mrs MunroSchool Closure Day
Mrs MunroWednesdayWorking Day
Mrs MunroSchool Closure Day
Mrs MunroThursdayWorking Day
Mrs MunroThursdayWorking Day
Mrs MunroSchool Closure Day
Mrs MunroSchool 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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3/30/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4/4/2018[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3/30/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4/5/2018[/TD]

[TD="align: right"]5.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/2/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/2/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/3/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/3/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4/4/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4/5/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]4/5/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]100156[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/6/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1234567[/TD]

[TD="align: right"]101453[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4/6/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=TEXT(N2,"dddd")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]=SUMIFS(D:D,A:A,$K$2,E:E,N2,G:G,$L$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
nburaq
  • Solved
Calculate interval between dates based on input at another columns
Replies
2
Views
378
awoohaw
awoohaw
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
648
Peter_SSs
Peter_SSs
L
  • Question Question
Distinct count based on 2 columns in a Pivot Table
Replies
0
Views
482
leefletcher
L
K
  • Solved
Excel formula in two spots of same work sheet not working properly
Replies
7
Views
245
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top