Formula Help (times)

Y

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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
Marcelo Branco said:
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.
Click to expand...

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
Marcelo Branco said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

J
  • Question Question
Sum only when time overlaps
Replies
1
Views
274
awoohaw
awoohaw
C
  • Question Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
733
Scott Huish
Scott Huish
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
258
tezza
tezza
M
  • Question Question
SLA Time Calculation
Replies
5
Views
306
felixstraube
F
L
  • Solved
Calculate start time from end time with multiple times per day for each ID
2
Replies
10
Views
242
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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