Help - Countif and sumif

U

ur_uncle

New Member
Joined
Jul 22, 2023
Messages
8
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
I am trying to counif and create a cumilative flow over headcount based on the day. Sample is below:

Trying to show how many employees are working at any given moment as such: I dont want to double count but just show x amount of people are work between these times
1/17/22​
5:00:00 AM​
5:59:59 AM​
6:00:00 AM​
6:59:59 AM​
7:00:00 AM​
7:59:59 AM​
8:00:00 AM​
8:59:59 AM​
9:00:00 AM​
9:59:59 AM​
10:00:00 AM​
10:59:59 AM​
11:00:00 AM​
11:59:59 AM​
12:00:00 PM​
12:59:59 PM​
1:00:00 PM​
1:59:59 PM​
2:00:00 PM​
2:59:59 PM​
3:00:00 PM​
3:59:59 PM​
4:00:00 PM​
4:59:59 PM​
5:00:00 PM​
5:59:59 PM​
6:00:00 PM​
6:59:59 PM​
7:00:00 PM​
7:59:59 PM​
8:00:00 PM​
8:59:59 PM​
9:00:00 PM​
9:59:59 PM​
10:00:00 PM​
10:59:59 PM​
11:00:00 PM​
11:59:59 PM​
12:00:00 AM​
12:59:59 AM​

1/17/22​
5:00:00 AM​
1:19:00 PM​
1/17/22​
7:00:00 AM​
4:00:00 PM​
1/17/22​
7:00:00 AM​
8:01:00 PM​
1/17/22​
8:00:00 AM​
10:08:00 PM​
1/17/22​
8:00:00 AM​
10:02:00 AM​
1/17/22​
8:00:00 AM​
10:00:00 AM​
1/17/22​
8:00:00 AM​
10:39:00 PM​
1/17/22​
8:00:00 AM​
9:36:00 AM​
1/17/22​
8:00:00 AM​
8:49:00 AM​
1/17/22​
8:00:00 AM​
4:57:00 PM​
1/17/22​
8:00:00 AM​
8:48:00 PM​
1/17/22​
8:00:00 AM​
8:47:00 PM​
1/17/22​
8:00:00 AM​
8:46:00 PM​
1/17/22​
8:00:00 AM​
2:05:00 PM​
1/17/22​
8:00:00 AM​
8:39:00 PM​
1/17/22​
10:00:00 AM​
4:07:00 PM​
1/17/22​
10:00:00 AM​
4:34:00 PM​
1/17/22​
10:00:00 AM​
8:53:00 PM​
1/17/22​
10:00:00 AM​
8:48:00 PM​
1/17/22​
10:00:00 AM​
5:27:00 PM​
1/17/22​
10:00:00 AM​
10:12:00 PM​
1/17/22​
10:00:00 AM​
8:53:00 PM​
1/17/22​
10:00:00 AM​
8:14:00 PM​
1/17/22​
10:00:00 AM​
8:39:00 PM​
1/17/22​
10:00:00 AM​
3:57:00 PM​
1/17/22​
10:00:00 AM​
8:53:00 PM​
1/17/22​
10:00:00 AM​
7:51:00 PM​
1/17/22​
10:00:00 AM​
8:49:00 PM​
1/17/22​
10:00:00 AM​
7:55:00 PM​
1/17/22​
10:00:00 AM​
5:47:00 PM​
1/17/22​
10:00:00 AM​
9:31:00 PM​
1/17/22​
10:00:00 AM​
3:55:00 PM​
1/17/22​
10:00:00 AM​
10:50:00 PM​
1/17/22​
10:00:00 AM​
9:56:00 PM​
1/17/22​
10:00:00 AM​
10:09:00 PM​
1/17/22​
10:00:00 AM​
11:11:00 PM​
1/17/22​
10:00:00 AM​
9:57:00 PM​
1/17/22​
10:15:00 AM​
3:10:00 PM​
1/17/22​
11:00:00 AM​
3:41:00 PM​
1/17/22​
11:00:00 AM​
11:39:00 PM​
1/17/22​
11:00:00 AM​
4:57:00 PM​
1/17/22​
11:00:00 AM​
9:01:00 PM​
1/17/22​
11:00:00 AM​
3:57:00 PM​
1/17/22​
11:00:00 AM​
10:48:00 PM​
1/17/22​
11:00:00 AM​
9:56:00 PM​
1/17/22​
11:00:00 AM​
10:47:00 PM​
1/17/22​
11:00:00 AM​
4:41:00 PM​
1/17/22​
12:00:00 PM​
7:53:00 PM​
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Try
Excel Formula: 
=COUNTIFS(DateColumn,DateSlot,EndTimeColumn,">="&StartTimeSlot,StartTimeColumn,"<="&EndTimeSlot)
?
 
Upvote 0
tried that, currently have this but not working exaclty and doesnt provide cummilative count.

=COUNTIFS($C$2:$C$10000, $W$1, $G$2:$G$10000, "<="&TIME(HOUR($T2), MINUTE($T2), SECOND($T2)), $J$2:$J$10000, ">="&TIME(HOUR($U2), MINUTE($U2), SECOND($U2)))
 
Upvote 0
Yeah
date data - c2 as date
start time - g2 as time
end time - j2 as time

v1 is exact date to match, date format
t2 - start time as time - i.e. 5:00am
u2 - end time as time - i.e. 5:59am
 
Upvote 0
And the result you would want for your above sample data is?
 
Upvote 0
For the record, this is what I have at the moment

Book1
ABCDEFGH
15:00:00 AM5:59:59 AM11/17/225:00:00 AM1:19:00 PM
26:00:00 AM6:59:59 AM11/17/227:00:00 AM4:00:00 PM
37:00:00 AM7:59:59 AM31/17/227:00:00 AM8:01:00 PM
48:00:00 AM8:59:59 AM151/17/228:00:00 AM10:08:00 PM
59:00:00 AM9:59:59 AM141/17/228:00:00 AM10:02:00 AM
610:00:00 AM10:59:59 AM361/17/228:00:00 AM10:00:00 AM
711:00:00 AM11:59:59 AM431/17/228:00:00 AM10:39:00 PM
812:00:00 PM12:59:59 PM431/17/228:00:00 AM9:36:00 AM
91:00:00 PM1:59:59 PM431/17/228:00:00 AM8:49:00 AM
102:00:00 PM2:59:59 PM421/17/228:00:00 AM4:57:00 PM
113:00:00 PM3:59:59 PM411/17/228:00:00 AM8:48:00 PM
124:00:00 PM4:59:59 PM361/17/228:00:00 AM8:47:00 PM
135:00:00 PM5:59:59 PM301/17/228:00:00 AM8:46:00 PM
146:00:00 PM6:59:59 PM281/17/228:00:00 AM2:05:00 PM
157:00:00 PM7:59:59 PM281/17/228:00:00 AM8:39:00 PM
168:00:00 PM8:59:59 PM261/17/2210:00:00 AM4:07:00 PM
179:00:00 PM9:59:59 PM141/17/2210:00:00 AM4:34:00 PM
1810:00:00 PM10:59:59 PM91/17/2210:00:00 AM8:53:00 PM
1911:00:00 PM11:59:59 PM21/17/2210:00:00 AM8:48:00 PM
2012:00:00 AM12:59:59 AM01/17/2210:00:00 AM5:27:00 PM
211/17/2210:00:00 AM10:12:00 PM
221/17/2210:00:00 AM8:53:00 PM
231/17/2210:00:00 AM8:14:00 PM
241/17/2210:00:00 AM8:39:00 PM
251/17/2210:00:00 AM3:57:00 PM
261/17/2210:00:00 AM8:53:00 PM
271/17/2210:00:00 AM7:51:00 PM
281/17/2210:00:00 AM8:49:00 PM
291/17/2210:00:00 AM7:55:00 PM
301/17/2210:00:00 AM5:47:00 PM
311/17/2210:00:00 AM9:31:00 PM
321/17/2210:00:00 AM3:55:00 PM
331/17/2210:00:00 AM10:50:00 PM
341/17/2210:00:00 AM9:56:00 PM
351/17/2210:00:00 AM10:09:00 PM
361/17/2210:00:00 AM11:11:00 PM
371/17/2210:00:00 AM9:57:00 PM
381/17/2210:15:00 AM3:10:00 PM
391/17/2211:00:00 AM3:41:00 PM
401/17/2211:00:00 AM11:39:00 PM
411/17/2211:00:00 AM4:57:00 PM
421/17/2211:00:00 AM9:01:00 PM
431/17/2211:00:00 AM3:57:00 PM
441/17/2211:00:00 AM10:48:00 PM
451/17/2211:00:00 AM9:56:00 PM
461/17/2211:00:00 AM10:47:00 PM
471/17/2211:00:00 AM4:41:00 PM
481/17/2212:00:00 AM7:53:00 PM
Sheet8
Cell Formulas
RangeFormula
C1:C20C1=COUNTIFS(F:F,"1/17/22",H:H,">="&A1,G:G,"<="&B1)
 
Upvote 0
Hey so I tried it and still doesnt seem to work, check this out:

Time Card Report Analysis.xlsx
ABCDEFHIJKLMN
1DateWork StartWork EndRegOTTOTAL Paid HRS1/17/22
21/17/225:00:00 AM1:19:00 PM8.320.008.325:00:00 AM5:59:59 AM0
31/17/227:00:00 AM4:00:00 PM0.009.009.006:00:00 AM6:59:59 AM0
41/17/227:00:00 AM8:01:00 PM11.620.0011.627:00:00 AM7:59:59 AM0
51/17/228:00:00 AM10:08:00 PM14.020.0014.028:00:00 AM8:59:59 AM0
61/17/228:00:00 AM10:02:00 AM2.030.002.039:00:00 AM9:59:59 AM0
71/17/228:00:00 AM10:00:00 AM2.000.002.0010:00:00 AM10:59:59 AM0
81/17/228:00:00 AM10:39:00 PM9.650.009.6511:00:00 AM11:59:59 AM0
91/17/228:00:00 AM9:36:00 AM1.600.001.6012:00:00 PM12:59:59 PM0
101/17/228:00:00 AM8:49:00 AM0.820.000.821:00:00 PM1:59:59 PM0
111/17/228:00:00 AM4:57:00 PM1.607.358.952:00:00 PM2:59:59 PM0
121/17/228:00:00 AM8:48:00 PM11.800.0011.803:00:00 PM3:59:59 PM0
131/17/228:00:00 AM8:47:00 PM8.030.008.034:00:00 PM4:59:59 PM0
141/17/228:00:00 AM8:46:00 PM4.930.004.935:00:00 PM5:59:59 PM0
151/17/228:00:00 AM2:05:00 PM6.030.056.086:00:00 PM6:59:59 PM0
161/17/228:00:00 AM8:39:00 PM6.150.006.157:00:00 PM7:59:59 PM0
171/17/2210:00:00 AM4:07:00 PM0.925.206.128:00:00 PM8:59:59 PM0
181/17/2210:00:00 AM4:34:00 PM4.272.306.579:00:00 PM9:59:59 PM0
191/17/2210:00:00 AM8:53:00 PM0.0010.1810.1810:00:00 PM10:59:59 PM0
201/17/2210:00:00 AM8:48:00 PM9.530.009.5311:00:00 PM11:59:59 PM0
211/17/2210:00:00 AM5:27:00 PM6.450.006.4512:00:00 AM12:59:59 AM0
2022-01-17
Cell Formulas
RangeFormula
N2:N21N2=COUNTIFS(A:A,"1/17/22",B:B,">="&L2,C:C,"<="&M2)
 
Upvote 1
You have it the other way round, the work END column must be greater than the start time and the work START column must be less than the end time. Swap the B:B and C:C references.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
310
Zee996
Z
R
  • Question
Unified DDMMYY with variable data
2
Replies
11
Views
292
Alex Blakenburg
A
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
87
ChetanPuri
C
C
  • Solved
Calculate difference b/w numbers
Replies
3
Views
147
Peter_SSs
Peter_SSs
C
  • Question
Condition calculation
Replies
1
Views
146
shknbk2
S

Forum statistics

Threads
1,215,140
Messages
6,123,269
Members
449,093
Latest member
Vincent Khandagale

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