+ Reply to Thread
Results 1 to 6 of 6

Count times text in cells but ignore formula

  1. 12-20-2022, 08:52 AM #1
    Morleyb
    Morleyb is offline
    Registered User
    Join Date
    06-15-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    37

    Count times text in cells but ignore formula

    Hi All

    I have created a spreadsheet which includes a booking time and a arrival time

    I have used a formula to only show text if the arrival time is either over 1 hour early or 1 hour late

    now I want to use a filter to only show the rows where this occurs

    In the attachment i would like column W to count the cells and return with 1 if a time appears on the same row in either column U or V

    this way I can filter column W to hide the rows I don't need to see

    Tab "Results" show how I would like it to look after this is working

    I am open to ideas if there is an easier way

    Thanks for taking the time to read and reply
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-20-2022, 09:38 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,707

    Re: Count times text in cells but ignore formula

    Administrative Note:

    Is your forum profile showing the version of Excel that you need this to work for?

    Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

    Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 12-20-2022, 10:09 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Count times text in cells but ignore formula

    I deleted ALL conditional formatting in U & V.

    U5, copied down:
    =IF(K5="","",IF(K5<(A5- TIME(1,0,0)),A5-K5,""))

    V5, copied down:
    =IF(K5="","",IF(K5>A5+TIME(1,0,0),(K5-A5),""))

    W5 copied down:
    =IF(OR(U5<>"",V5<>""),1,0)
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  4. 12-20-2022, 10:30 AM #4
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Count times text in cells but ignore formula

    In W5

    =1*(COUNTIF(U5:V5,">0")>0)

    I have changed formulas in U an V column. Unwanted spaces also removed.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  5. 12-21-2022, 01:56 AM #5
    Morleyb
    Morleyb is offline
    Registered User
    Join Date
    06-15-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    37

    Re: Count times text in cells but ignore formula

    Thanks for this

    Gave me what I asked for and more :-)
    Reply With QuoteRegister To Reply
  6. 12-21-2022, 01:56 AM #6
    Morleyb
    Morleyb is offline
    Registered User
    Join Date
    06-15-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    37

    Re: Count times text in cells but ignore formula

    Thanks for this

    Exactly what i asked for
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need formula to count how many times different text patterns occur within a cell
    By ck248 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2017, 12:04 AM
  2. [SOLVED] how to count cells with values but ignore blank cells with formula and in a month?
    By Imran Magsi in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-17-2017, 03:58 AM
  3. how to ignore the numeric cells when giving formula to count cells that contains text only
    By rajuj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2014, 10:48 PM
  4. Count the number of times a particular text appears in group of cells
    By L.LEE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2013, 04:16 AM
  5. [SOLVED] Count how many times a month appear but ignore blank cells
    By mdithi in forum Excel General
    Replies: 2
    Last Post: 03-26-2012, 05:38 PM
  6. Count how many times a month appear but ignore blank cells
    By ozzom_2000 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-24-2011, 10:46 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1