CountIfs Time Stamps

JCDCP123

New Member
Joined
Mar 19, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Having trouble with a CountIfs function.

I am trying to answer the question: At any given time (down to the second), how many people were in attendance at our online course?

My spreadsheet looks like this:
ABCDEF
SessionStartSessionEndConcurrent Viewers
7:00:00 PM​
7:02:17 PM​
Time 1Time 2Count
7:00:00 PM​
7:02:10 PM​
7:00:00 PM​
7:00:01 PM​
0​
7:00:00 PM​
7:07:34 PM​
7:00:01 PM​
7:00:02 PM​
0​
7:00:00 PM​
7:07:19 PM​
7:00:02 PM​
7:00:03 PM​
0​
7:00:00 PM​
7:07:25 PM​
7:00:03 PM​
7:00:04 PM​
0​
7:00:00 PM​
7:03:12 PM​
7:00:04 PM​
7:00:05 PM​
0​
7:16:00 PM​
8:00:00 PM​
7:00:05 PM​
7:00:06 PM​
0​
7:16:04 PM​
7:16:11 PM​
7:00:06 PM​
7:00:07 PM​
0​

And the formula in Column F is:
=COUNTIFS(A:A,"=<"&D3,B:B,">"&E3)

Unfortunately, as you can see, it's returning a value of 0 in all cells.

All the cells with timestamps are formatted as Time only. There is no date data included in there (as far as I can tell).

Does anyone know how I could fix this?
Thanks in advance.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You have the "=<" backwards, should be:

Excel Formula:
=COUNTIFS(A:A,"<="&D3,B:B,">"&E3)
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, glad to help.

PS: you're supposed to mark the post that gave you the answer as solution, marking your own post as solution flags it with the MODs, unless you answered your own question.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,407
@JCDCP123 - I just switched the marked solution with the correct one that you also confirmed. Next time, that would be great if you could mark the solution post that answered the problem instead of your final post. It will help future readers to find the solution post easier.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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
Top