Countifs Mutiple Columns and Criteria Times

Couple of questions...
If person is there from 7:00 to 7:45 and you count in 15 min interval... Should it not exclude 7:45 (7:45 to 8:00 interval)? Since the person left at 7:45.

Also, how did you enter time value in each area? While E7 & J14 both show 9:30. Stored value isn't exact match.

In this case you'll need to use MROUND(Cell,1/24/60) to round to nearest minute to find match.

However, since you can't use MROUND() on range. You'll either need helper column(s) or use lambda to apply calculation.

Sample formula to handle time rounding for range... This uses ">=" for end time to match your manual result.

In K3: Copy down and across.

Please Login or Register  to view this content.

There may be better way to do this, but this should get you the result you want.

Edit: If your time values are exact matches... formula will be simple COUNTIFS + COUNTIFS.

Please Login or Register  to view this content.

Please try in K3 and copy to the right and down:

Formula:

Please Login or Register  to view this content.

Sometimes there is a difference of 1 compared to the expected results.
This is because the same times in the sheet are not recorded exactly with the same values for some reason.

Example the 9:30 in D8 has a value of 0.395833333333333,
but the 9:30 in J14 has the value of 0.395833333333334.

The latter is strange. When you re-record 9:30 in J14, it also gets the value of 0.395833333333333.

You can prevent these types of rounding differences by increasing all times in column J by 1 minute (or 1 second).

@alo2279

Yes, drag down operation will introduce floating point error on small decimal values (i.e. time value).
Typically, I perform most of my time intelligence in data model and use methods in PQ & DAX to handle rounding issues.

In your case, using either BYROW iteration using LAMBDA function, or adding helper column as I've stated can handle issue arising from floating point error by applying MROUND(Timevalue,1/24/60).

You are welcome.
If you are satisfied with the solution provided. Please mark the thread as solved using thread tools found at top of your initial post.