COUNTIFS Not calculating correctly?

[RedOctoberKnight]

Good Morning All,

I'm trying to create a worksheet that quickly analyzes schedules for work. The problem is, for whatever reason, my total count is off by 1.

I'm trying to find out how many occurrences occur between two times and specific day of the week.

I'm using the following formula where column "R" is the actual occurrence time, "U" is the beginning time, "V" is the end time, and "S" is the day of the week matching up with the day of the week in W1

=COUNTIFS($R$2:$R$1048576,">="&$U3,$R$2:$R$1048576,"<="&$V3,$S$2:$S$1048576,W$1)

When I drag the formula down to count between all specified times, everything seems to come out fine but when i total up the occurrences, I always seem to be 1 short from what the actual occurrences are. For example, i know there are 50 occurrences but when I add them all up using the above formula to find how many occur between each hour, I end up with only 49.

Anyone have any ideas or possibly suggestions for a different formula?


Thanks in advance!

 

[Fluff]

What cell is that formula in?

 

[RedOctoberKnight]

What cell is that formula in?

Formula is in W3 and then I drag it down

 

[arthurbr]

And now that one can use Excel Tables, why refer to entire columns?

 

[Fluff]

Formula is in W3 and then I drag it down

Then why are your ranges starting on row2? Is that the reason the count is off

 

[RedOctoberKnight]

Then why are your ranges starting on row2? Is that the reason the count is off

My data starts in row 2. The beginning and end time starts in row 3. So U3 is the beginning time and V3 is the end time for which I want to search between the two times. The formula is in W3.

 

[number kruncher]

Uploading a sample of your data may help answer a lot of these questions more effectively.

Use XL2BB to do this.

 

[Fluff]

Are you trying to compare every row to U3 & V3?

 

[RedOctoberKnight]

Thank you all for your responses but I believe I have it figured out. I believe it probably has something to do with a rounding issue because when I changed my search times from on the hour to a minute before (5:00-5:59 changed to 4:59-5:58) it works correctly. More specifically, I think it was an occurrence that occurred at 19:00 that was failing to be counted.

 

[Fluff]

Glad you sorted it & thanks for the feedback.