+ Reply to Thread
Results 1 to 4 of 4

Excluding hidden rows from =countif and filtering via a range of dates in a cell of text

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    2

    Excluding hidden rows from =countif and filtering via a range of dates in a cell of text

    I have two problems:
    First problem is how can I filter my xml calender for entries between two dates when the only cell with the date displays it like:
    "When: Sun Oct 27, 2013 7:45pm to 10pm 
    GMT<br>


    <br>Event Status: confirmed"
    I can get individual date using an advanced filter on this column using "*Oct*27*" (this will only show entries for the 25th of October) but putting > \ < doesn't work

    Second problem is that when I use an =IFCOUNT to count the amount of event, it doesn't take into account the filter so just counts the total amount of this event as opposed to just the filtered ones.
    (Sorry, this may be a bit confusing but I'm not to sure how to properly describe my problems)

    Any help would be greatly appreciated :D

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Excluding hidden rows from =countif and filtering via a range of dates in a cell of te

    Could you just try upload the sample file and desired result?

    Azumi

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excluding hidden rows from =countif and filtering via a range of dates in a cell of te

    Hi and welcome ot the forum

    If that really IS what the data looks like in 1 cell...
    "When: Sun Oct 27, 2013 7:45pm to 10pm&nbsp

    Then that is text, not a date, and you cannot to > < on text. You will need to extract or convert that to a date before you can do any math-type operations on it

    Assuming that is in A1, try using this...
    =DATEVALUE(MID(A1,SEARCH(":",A1,1)+6,SEARCH("xx",SUBSTITUTE(A1," ","xx",5),1)-SEARCH("xx",SUBSTITUTE(A1," ","xx",2),1)))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-29-2013
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Excluding hidden rows from =countif and filtering via a range of dates in a cell of te

    I've manages to get the month (Oct/Nov) and the date (27,28) using =MID but when I try and filter the date column with <25 (or something similar) nothing comes up

+ 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] Sumif & Countif (excluding hidden rows)
    By JimExcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2013, 07:06 AM
  2. [SOLVED] Display range excluding hidden rows in
    By jmethejedi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2012, 07:06 PM
  3. Average of range of dates with hidden / filtered rows
    By sullve07 in forum Excel General
    Replies: 3
    Last Post: 11-21-2010, 03:24 PM
  4. SUMIF Excluding Hidden Rows
    By bleat0r in forum Excel General
    Replies: 3
    Last Post: 09-16-2010, 02:36 AM
  5. SUMIF Excluding hidden rows
    By TREMA in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2008, 08:54 PM

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