Formula Help (times)

yessir

Board Regular
Joined
Jun 7, 2019
Messages
103
Office Version
  1. 2021
Platform
  1. MacOS
Is there a formula that can be ran on these values to then return something similar to the sample table provided below. The objective of this formula wold be to break down minute by minute is there are 0,1,2, or more times overlapping.

[TABLE="width: 115"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"><tbody>[TR]
[TD="width: 80, bgcolor: transparent"]Times[/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12:45[/TD]
[TD="bgcolor: transparent, align: right"]13:01[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12:55[/TD]
[TD="bgcolor: transparent, align: right"]13:05
[/TD]
[/TR]
</tbody>[/TABLE]


From this ^ to this (below)


[TABLE="width: 960"]
<colgroup><col width="64" style="width: 48pt;" span="20"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]12:43[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:44[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:45[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:46[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:47[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:48[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:49[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:50[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:51[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:52[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:53[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:54[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:55[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:56[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:57[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:58[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:59[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13:00[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13:01[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13:02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td][td="bgcolor: #DCE6F1"]
D
[/td][td="bgcolor: #DCE6F1"]
E
[/td][td="bgcolor: #DCE6F1"]
F
[/td][td="bgcolor: #DCE6F1"]
G
[/td][td="bgcolor: #DCE6F1"]
H
[/td][td="bgcolor: #DCE6F1"]
I
[/td][td="bgcolor: #DCE6F1"]
J
[/td][td="bgcolor: #DCE6F1"]
K
[/td][td="bgcolor: #DCE6F1"]
L
[/td][td="bgcolor: #DCE6F1"]
M
[/td][td="bgcolor: #DCE6F1"]
N
[/td][td="bgcolor: #DCE6F1"]
O
[/td][td="bgcolor: #DCE6F1"]
P
[/td][td="bgcolor: #DCE6F1"]
Q
[/td][td="bgcolor: #DCE6F1"]
R
[/td][td="bgcolor: #DCE6F1"]
S
[/td][td="bgcolor: #DCE6F1"]
T
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1
[/td][td]
Times​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2
[/td][td]
12:45​
[/td][td]
13:01​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3
[/td][td]
12:55​
[/td][td]
13:05​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6
[/td][td]
12:43​
[/td][td]
12:44​
[/td][td]
12:45​
[/td][td]
12:46​
[/td][td]
12:47​
[/td][td]
12:48​
[/td][td]
12:49​
[/td][td]
12:50​
[/td][td]
12:51​
[/td][td]
12:52​
[/td][td]
12:53​
[/td][td]
12:54​
[/td][td]
12:55​
[/td][td]
12:56​
[/td][td]
12:57​
[/td][td]
12:58​
[/td][td]
12:59​
[/td][td]
13:00​
[/td][td]
13:01​
[/td][td]
13:02​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7
[/td][td]
0​
[/td][td]
0​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
2​
[/td][td]
2​
[/td][td]
2​
[/td][td]
2​
[/td][td]
2​
[/td][td]
2​
[/td][td]
2​
[/td][td]
1​
[/td][/tr]
[/table]


Formula in A7 copied across
=COUNTIFS($A$2:$A$3,"<="&A$6,$B$2:$B$3,">="&A$6)

M.
 
Upvote 0
Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
D
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
E
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
F
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
G
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
H
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
I
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
J
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
K
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
L
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
M
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
N
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
O
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
P
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
Q
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
R
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
S
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
T
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1
[/TD]
[TD]
Times​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2
[/TD]
[TD]
12:45​
[/TD]
[TD]
13:01​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3
[/TD]
[TD]
12:55​
[/TD]
[TD]
13:05​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
6
[/TD]
[TD]
12:43​
[/TD]
[TD]
12:44​
[/TD]
[TD]
12:45​
[/TD]
[TD]
12:46​
[/TD]
[TD]
12:47​
[/TD]
[TD]
12:48​
[/TD]
[TD]
12:49​
[/TD]
[TD]
12:50​
[/TD]
[TD]
12:51​
[/TD]
[TD]
12:52​
[/TD]
[TD]
12:53​
[/TD]
[TD]
12:54​
[/TD]
[TD]
12:55​
[/TD]
[TD]
12:56​
[/TD]
[TD]
12:57​
[/TD]
[TD]
12:58​
[/TD]
[TD]
12:59​
[/TD]
[TD]
13:00​
[/TD]
[TD]
13:01​
[/TD]
[TD]
13:02​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
7
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in A7 copied across
=COUNTIFS($A$2:$A$3,"<="&A$6,$B$2:$B$3,">="&A$6)

M.

That formula did work. Is ther a way to edit it to where it produces something like this when there are more times and places? Examples below.

[TABLE="width: 156"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Times[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent, align: right"]12:45[/TD]
[TD="bgcolor: transparent, align: right"]13:01[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent, align: right"]12:55[/TD]
[TD="bgcolor: transparent, align: right"]13:05[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent, align: right"]12:57[/TD]
[TD="bgcolor: transparent, align: right"]13:02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent, align: right"]12:44[/TD]
[TD="bgcolor: transparent, align: right"]12:54[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent, align: right"]12:47[/TD]
[TD="bgcolor: transparent, align: right"]13:00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent, align: right"]12:53[/TD]
[TD="bgcolor: transparent, align: right"]13:02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent, align: right"]12:55[/TD]
[TD="bgcolor: transparent, align: right"]13:00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT
[/TD]
[TD="bgcolor: transparent, align: right"]12:57
[/TD]
[TD="bgcolor: transparent, align: right"]13:00
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 1027"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="17"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:43[/TD]
[TD="width: 80, bgcolor: transparent, align: right"]12:44[/TD]
[TD="width: 73, bgcolor: transparent, align: right"]12:45[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:46[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:47[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:48[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:49[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:50[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:51[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:52[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:53[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:54[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:55[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:56[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:57[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:58[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:59[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13:00[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13:01[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13:02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
All you have to do is to add a new condition

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[TD="bgcolor: #DCE6F1"]
G
[/TD]
[TD="bgcolor: #DCE6F1"]
H
[/TD]
[TD="bgcolor: #DCE6F1"]
I
[/TD]
[TD="bgcolor: #DCE6F1"]
J
[/TD]
[TD="bgcolor: #DCE6F1"]
K
[/TD]
[TD="bgcolor: #DCE6F1"]
L
[/TD]
[TD="bgcolor: #DCE6F1"]
M
[/TD]
[TD="bgcolor: #DCE6F1"]
N
[/TD]
[TD="bgcolor: #DCE6F1"]
O
[/TD]
[TD="bgcolor: #DCE6F1"]
P
[/TD]
[TD="bgcolor: #DCE6F1"]
Q
[/TD]
[TD="bgcolor: #DCE6F1"]
R
[/TD]
[TD="bgcolor: #DCE6F1"]
S
[/TD]
[TD="bgcolor: #DCE6F1"]
T
[/TD]
[TD="bgcolor: #DCE6F1"]
U
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD][/TD]
[TD]
Times​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD]
OOO​
[/TD]
[TD]
12:45​
[/TD]
[TD]
13:01​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
OOO​
[/TD]
[TD]
12:55​
[/TD]
[TD]
13:05​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
PPP​
[/TD]
[TD]
12:57​
[/TD]
[TD]
13:02​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD]
PPP​
[/TD]
[TD]
12:44​
[/TD]
[TD]
12:54​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD]
PPP​
[/TD]
[TD]
12:47​
[/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD]
[TD]
TTT​
[/TD]
[TD]
12:53​
[/TD]
[TD]
13:02​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
[/TD]
[TD]
TTT​
[/TD]
[TD]
12:55​
[/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
[/TD]
[TD]
TTT​
[/TD]
[TD]
12:57​
[/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13
[/TD]
[TD][/TD]
[TD]
12:43​
[/TD]
[TD]
12:44​
[/TD]
[TD]
12:45​
[/TD]
[TD]
12:46​
[/TD]
[TD]
12:47​
[/TD]
[TD]
12:48​
[/TD]
[TD]
12:49​
[/TD]
[TD]
12:50​
[/TD]
[TD]
12:51​
[/TD]
[TD]
12:52​
[/TD]
[TD]
12:53​
[/TD]
[TD]
12:54​
[/TD]
[TD]
12:55​
[/TD]
[TD]
12:56​
[/TD]
[TD]
12:57​
[/TD]
[TD]
12:58​
[/TD]
[TD]
12:59​
[/TD]
[TD]
13:00​
[/TD]
[TD]
13:01​
[/TD]
[TD]
13:02​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14
[/TD]
[TD]
OOO​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15
[/TD]
[TD]
PPP​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16
[/TD]
[TD]
TTT​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
2​
[/TD]
[TD] 1
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B14 copied across and down
=COUNTIFS($A$2:$A$9,$A14,$B$2:$B$9,"<="&B$13,$C$2:$C$9,">="&B$13)

M.
 
Last edited:
Upvote 0
All you have to do is to add a new condition

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
D
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
E
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
F
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
G
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
H
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
I
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
J
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
K
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
L
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
M
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
N
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
O
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
P
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
Q
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
R
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
S
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
T
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
U
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1
[/TD]
[TD][/TD]
[TD]
Times​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2
[/TD]
[TD]
OOO​
[/TD]
[TD]
12:45​
[/TD]
[TD]
13:01​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3
[/TD]
[TD]
OOO​
[/TD]
[TD]
12:55​
[/TD]
[TD]
13:05​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4
[/TD]
[TD]
PPP​
[/TD]
[TD]
12:57​
[/TD]
[TD]
13:02​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
5
[/TD]
[TD]
PPP​
[/TD]
[TD]
12:44​
[/TD]
[TD]
12:54​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
6
[/TD]
[TD]
PPP​
[/TD]
[TD]
12:47​
[/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
7
[/TD]
[TD]
TTT​
[/TD]
[TD]
12:53​
[/TD]
[TD]
13:02​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
8
[/TD]
[TD]
TTT​
[/TD]
[TD]
12:55​
[/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
9
[/TD]
[TD]
TTT​
[/TD]
[TD]
12:57​
[/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
13
[/TD]
[TD][/TD]
[TD]
12:43​
[/TD]
[TD]
12:44​
[/TD]
[TD]
12:45​
[/TD]
[TD]
12:46​
[/TD]
[TD]
12:47​
[/TD]
[TD]
12:48​
[/TD]
[TD]
12:49​
[/TD]
[TD]
12:50​
[/TD]
[TD]
12:51​
[/TD]
[TD]
12:52​
[/TD]
[TD]
12:53​
[/TD]
[TD]
12:54​
[/TD]
[TD]
12:55​
[/TD]
[TD]
12:56​
[/TD]
[TD]
12:57​
[/TD]
[TD]
12:58​
[/TD]
[TD]
12:59​
[/TD]
[TD]
13:00​
[/TD]
[TD]
13:01​
[/TD]
[TD]
13:02​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
14
[/TD]
[TD]
OOO​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
15
[/TD]
[TD]
PPP​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
16
[/TD]
[TD]
TTT​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
2​
[/TD]
[TD] 1
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B14 copied across and down
=COUNTIFS($A$2:$A$9,$A14,$B$2:$B$9,"<="&B$13,$C$2:$C$9,">="&B$13)

M.

When using the values and times in the below table the formula doesn't seem to be assigning value to when times overlap

[TABLE="width: 168"]
<colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" span="3"> <tbody>[TR]
[TD="width: 74, bgcolor: transparent"]OOO[/TD]
[TD="width: 74, bgcolor: transparent"]23:49[/TD]
[TD="width: 74, bgcolor: transparent"]5:31[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent"]17:49[/TD]
[TD="bgcolor: transparent"]18:20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent"]11:03[/TD]
[TD="bgcolor: transparent"]11:35[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent"]14:39[/TD]
[TD="bgcolor: transparent"]15:04[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent"]22:04[/TD]
[TD="bgcolor: transparent"]6:15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent"]16:01[/TD]
[TD="bgcolor: transparent"]16:28[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OOO[/TD]
[TD="bgcolor: transparent"]9:12[/TD]
[TD="bgcolor: transparent"]9:37[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent"]16:08[/TD]
[TD="bgcolor: transparent"]16:33[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent"]10:01[/TD]
[TD="bgcolor: transparent"]10:26[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PPP[/TD]
[TD="bgcolor: transparent"]11:57[/TD]
[TD="bgcolor: transparent"]12:22[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]23:35[/TD]
[TD="bgcolor: transparent"]8:00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]20:03[/TD]
[TD="bgcolor: transparent"]6:37[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]15:49[/TD]
[TD="bgcolor: transparent"]16:34[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]11:59[/TD]
[TD="bgcolor: transparent"]12:44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]9:48[/TD]
[TD="bgcolor: transparent"]10:34[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]13:57[/TD]
[TD="bgcolor: transparent"]14:44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]23:59[/TD]
[TD="bgcolor: transparent"]6:00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]12:37[/TD]
[TD="bgcolor: transparent"]13:15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]20:52[/TD]
[TD="bgcolor: transparent"]21:22[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]22:58[/TD]
[TD="bgcolor: transparent"]7:00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]12:57[/TD]
[TD="bgcolor: transparent"]13:27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]8:12[/TD]
[TD="bgcolor: transparent"]8:45[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]23:53[/TD]
[TD="bgcolor: transparent"]8:45[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]11:46[/TD]
[TD="bgcolor: transparent"]12:30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]18:11[/TD]
[TD="bgcolor: transparent"]18:50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TTT[/TD]
[TD="bgcolor: transparent"]14:29[/TD]
[TD="bgcolor: transparent"]15:07[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Last edited:
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

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