+ Reply to Thread
Results 1 to 11 of 11

Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "X"

  1. #1
    Registered User
    Join Date
    09-19-2019
    Location
    London
    MS-Off Ver
    2011
    Posts
    9

    Question Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "X"

    Hello people!

    First time posting here, and I've hit a bit of a hard wall.
    I have a column containing days of the week Mon-Sun, and another column containing specific times of when a particular event occurs.

    e.g.
    Column A | Column B
    Tuesday | 15:23
    Thursday | 10:18
    Wednesday | 21:46
    Tuesday | 15:02
    Monday | 03:15
    Friday | 19:08
    Tuesday | 09:51

    What I'm trying to generate is a formula that can look at both column A and column B, and count how many times on a Tuesday did the event take place between 15:00 and 15:59, (in the above example the outcome of this sought-after formula would be 2), and I don't know how to do it

    Any help would be thoroughly appreciated!

    Thank you so much

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    One way is this:=COUNTIFS($A$1:$A$7,"Tuesday",$B$1:$B$7,">="&0.625,$B$1:$B$7,"<="&0.6659)
    Times in excel are numbers converted. meaning that in the formula above 0.625 is 15:00 and 0.6659 is 15.59
    Click the * to say thanks.

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    You could also write it like this if it's easier to follow

    =COUNTIFS(A1:A7,"Tuesday",B1:B7,">="&((1/24)*15),B1:B7,"<"&((1/24)*16))

  4. #4
    Registered User
    Join Date
    09-19-2019
    Location
    London
    MS-Off Ver
    2011
    Posts
    9

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    OMG it worked! Thank you so much !!!

    Could you explain how the times are converted into numbers?

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    My formula makes it a bit explicit, but times in excel as stored as decimals. One day is 1. Therefore 1/24 is one hour.

  6. #6
    Registered User
    Join Date
    09-19-2019
    Location
    London
    MS-Off Ver
    2011
    Posts
    9

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    Aaah amazing, apologies, I hadn't seen your reply, Kyle123, when I initially responded, your formula is indeed quite explicit

    Thank you both!

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    The values can obviously be in 2 cells

    so if c1 contains 15:00
    and c2 contains 16:00

    =COUNTIFS(A1:A7,"Tuesday",B1:B7,">="&((1/24)*15),B1:B7,"<"&((1/24)*16))

    becomes

    =COUNTIFS(A1:A7,"Tuesday",B1:B7,">="&c1,B1:B7,"<"&c2)

  8. #8
    Registered User
    Join Date
    09-19-2019
    Location
    London
    MS-Off Ver
    2011
    Posts
    9

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    Ah yes ofc! Makes so much sense, thank you!

  9. #9
    Registered User
    Join Date
    09-19-2019
    Location
    London
    MS-Off Ver
    2011
    Posts
    9

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    Quote Originally Posted by Kyle123 View Post
    My formula makes it a bit explicit, but times in excel as stored as decimals. One day is 1. Therefore 1/24 is one hour.
    in the same way that excel stores times as decimals, does it do something similar with dates?

    for example, if I wanted to include the date 12/05/2019 in a this formula, what would it have to be written as?
    =IF(ISNUMBER(SEARCH("12/05/2019",E2)),"first delivery"," ")

    because this keeps giving me the value_if_false in the cell.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    This is a pretty good explanation of how Excel stores dates and times: http://www.cpearson.com/Excel/datetime.htm
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Registered User
    Join Date
    09-19-2019
    Location
    London
    MS-Off Ver
    2011
    Posts
    9

    Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

    Amazing thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Extracting the common values found in Column "A" & Column "B" into Column "C"
    By Asad Mir in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-18-2021, 06:36 AM
  2. countif to return "x" for unique & "xx" for duplicate values in a column
    By Shruder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2018, 05:31 PM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"
    By moexcelnew in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-30-2016, 03:07 PM
  5. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  6. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  7. COUNTIF column if cell contains both "text1" and "text2"
    By Quuador in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2012, 07:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1