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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
423
dshtesy
D
nburaq
  • Solved
Calculate interval between dates based on input at another columns
Replies
2
Views
386
awoohaw
awoohaw
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
664
Peter_SSs
Peter_SSs
L
  • Question Question
Distinct count based on 2 columns in a Pivot Table
Replies
0
Views
496
leefletcher
L
K
  • Solved
Excel formula in two spots of same work sheet not working properly
Replies
7
Views
249
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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