COUNTIF statement to count between two times spanning overnight

**lukela85** · 02-04-2017, 01:49 PM

Hi all,

I have this COUNTIF statement which counts how many times fall between 9am and 9pm, which works fine:

=COUNTIFS(F4:F34,">9:00",F4:F34,"<21:00")

I would also like to count how many times fall between 9pm and 9am, however the same COUNTIF statement doesn't work (switching 9:00 for 21:00 and 21:00 for 9:00).

Can anyone help?

Thanks,

Luke

**Pete_UK** · 02-04-2017, 02:14 PM

Do you have a date column that you can also refer to?

If not, you can use two COUNTIF terms - one looking for < 9:00 and the other looking for > 21:00 and add them together.

Hope this helps.

Pete

**lukela85** · 02-04-2017, 02:54 PM

Thanks for your nudge in the right direction Pete!

**Pete_UK** · 02-04-2017, 02:56 PM

You're welcome.

Pete

COUNTIF statement to count between two times spanning overnight

LinkBack

Thread Tools

Rate This Thread

Display

COUNTIF statement to count between two times spanning overnight

Re: COUNTIF statement to count between two times spanning overnight

Re: COUNTIF statement to count between two times spanning overnight

Re: COUNTIF statement to count between two times spanning overnight

Thread Information

Users Browsing this Thread

Similar Threads

Finding range between start times, spanning across midnight

Average of Times Spanning Midnight

[SOLVED] Long Countif statement Not to Count Blank Cells

[SOLVED] Count occurrences and sum values with times spanning over midnight.

[SOLVED] Using SUMPRODUCT or COUNTIF to count number of times a value occurs

If statement spanning range of cells

[SOLVED] COUNTIF:count the number of times

Bookmarks

Bookmarks

Posting Permissions