Counting a Time Range with COUNTIFS or SUMIFS

Josh08

New Member
Joined
Aug 23, 2019
Messages
6
Good Morning

I have an issue. I've been trying to create a formula to count the number of instances a particular time range appears in a column on a spreadsheet.

Column C contains manually entered times followed by AM or PM. One problem I've run into that may or may not be effecting the formula's I've tried is that the time formats are different in some columns.

Example
Data Type = General
12:17 PM

Example
Data Type = Custom
12:02:00 PM

I've tried many methods to make them all the same format. I would prefer the one with the General data type.

I tried converting the data type. Copying and pasting one with the format I wanted and change it in the cell, no luck.

=SUMIFS(A:A,">=6:00AM", A:A, "<3:00PM")

My desired results were not yielded from that formula. I've tried other formulas without the correct result either. I don't want to manually count over 350 times.
 
Without your file .. difficult to guess what is in front of your eyes ...

Most probably for 2nd shift ... you should either >= or <=

and your third shift should be the Total minus both 1st and 2nd Shift ...

I believe that adding the "=" has done the trick. Not so much for the formula that I had for the 3rd shift but instead I did what you suggested. Total - (1st shift _ 2nd shift). Now the totals are adding up. The 1 I was missing from 2nd shift populated by adding the = for the greater than or equal to and less than or equal to.

Thank you James006!
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I believe that adding the "=" has done the trick. Not so much for the formula that I had for the 3rd shift but instead I did what you suggested. Total - (1st shift _ 2nd shift). Now the totals are adding up. The 1 I was missing from 2nd shift populated by adding the = for the greater than or equal to and less than or equal to.

Thank you James006!

My suggestion in post #2 was to use those equalities:

=COUNTIFS(C2:C19,">=06:00",C2:C19,"<=15:00")
 
Upvote 0
I believe that adding the "=" has done the trick. Not so much for the formula that I had for the 3rd shift but instead I did what you suggested. Total - (1st shift _ 2nd shift). Now the totals are adding up. The 1 I was missing from 2nd shift populated by adding the = for the greater than or equal to and less than or equal to.

Thank you James006!

Glad you could fix your problem ...:)

Thanks for your Thanks
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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