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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
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

M
  • Solved
sumifs using date and time values
Replies
9
Views
245
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
430
myall_blues
myall_blues
A
  • Solved
Count entry by time
Replies
3
Views
169
admvlad
A
A
  • Question Question
Hi. I'm trying to fill 1 for the time between arrival and departure.
Replies
1
Views
159
Asbestos_Jen
Asbestos_Jen
J
  • Question Question
Finding Numbers from a Column Using Target Numbers
2
Replies
11
Views
414
Cubist
Cubist

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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