+ Reply to Thread
Results 1 to 7 of 7

Count with multiple criteria (Date, Time and Other Criteria)

  1. #1
    Registered User
    Join Date
    06-24-2018
    Location
    India
    MS-Off Ver
    2016
    Posts
    45

    Count with multiple criteria (Date, Time and Other Criteria)

    Hello All,

    I've has a "TaskName" in column M and "ActiveDate" in Column N.

    "Active Date" is a number and with format as 5/31/2018 10:46:14 AM

    I'm trying to count "date and time range", buy my date criteria and time criteria are in different cells.

    This the formula I have used which is giving me incorrect values.

    =COUNTIFS(TaskNm,F$7,ActvDt,">="&$E$4+$D8,ActvDt,"<"&$H$4+$E8)

    F7 = TaskName (I've defined name to it as shown in formula)

    =OFFSET('Raw Data'!$M$2,,,COUNTA('Raw Data'!$M$2:$M$9999))

    E4 & H4 are dates

    E4 & E8 are Times


    Untitled.png

    Please someone help me to solve my problem.

    Thank you and your solution will be great help
    Attached Files Attached Files
    Last edited by Naren_Kumar; 06-24-2018 at 03:47 PM. Reason: To add file

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,757

    Re: Count with multiple criteria (Date, Time and Other Criteria)

    Hi and welcome to the forum

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-24-2018
    Location
    India
    MS-Off Ver
    2016
    Posts
    45

    Re: Count with multiple criteria (Date, Time and Other Criteria)

    Please find the attachment

  4. #4
    Registered User
    Join Date
    06-24-2018
    Location
    India
    MS-Off Ver
    2016
    Posts
    45

    Re: Count with multiple criteria (Date, Time and Other Criteria)

    I cracked it. I got guidance from "Variatus - Teach Excel".

    Hi Variatus,

    Thank you so much!! You are an awesome Excel Guru.

    I cracked it by using 1 Helper cell.... I was trying it for 2 days and was struggling to crack it. But I remembered to take help from this site as I did it once.

    Well, I just two test helper cell to evaluate the conditions of "Active Time". Then I incorporated those conditions in Countifs.

    As you suggested I evaluated TRUES and FASLSES suggestion and cracked it

    By inducing below formula:

    =COUNTIFS($M$2:$M$165,AU$8,$N$2:$N$165,">="&DATE(YEAR($AS$5),MONTH($AS$5),DAY($AS$5)),$O$2:$O$165,">="&TIME(HOUR($AS10),MINUTE($AS10),SECOND($AS10)),$N$2:$N$165,"<"&DATE(YEAR($AT$5),MONTH($AT$5),DAY($AT$5)),$O$2:$O$165,"<"&TIME(HOUR($AT10),MINUTE($AT10),SECOND($AT10)))

    Sorry, I haven't used define name for arrays….

    If you can review the attachment and give me any better solution would be an another great learning from me.

    Thank you so much once again.
    Attached Files Attached Files
    Last edited by Naren_Kumar; 06-25-2018 at 03:15 AM.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Count with multiple criteria (Date, Time and Other Criteria)

    I cracked it by using 1 Helper cell.... I was trying it for 2 days and was struggling to crack it. But I remembered to take help from this site as I did it once.

    Well, I just two test helper cell to evaluate the conditions of "Active Time". Then I incorporated those conditions in Countifs.

    As you suggested I evaluated TRUES and FASLSES suggestion and cracked it
    Bravo!!!

    That is music to our ears.

    Your judicious use of helper columns is to be applauded as well as a clear understanding that dates and times are numbers.

    Having said that an offer of "better solution" I don't have.

    I can only offer further encouragement to explore alternatives to COUNTIFS. I offer the below. We often see SUMPRODUCT as one of those alternatives.

    See if this is of help.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Once again, good work.
    Dave

  6. #6
    Registered User
    Join Date
    06-24-2018
    Location
    India
    MS-Off Ver
    2016
    Posts
    45

    Re: Count with multiple criteria (Date, Time and Other Criteria)

    Its a nice sumproduct formula. Used without helper cells. Thank you so much!!

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Count with multiple criteria (Date, Time and Other Criteria)

    You are welcome. Thank you for the feedback and rep.

    There may be more suggestions coming from other members ...

+ 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] How to count / sum with criteria DATE & TIME ?
    By coba66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2018, 12:54 PM
  2. Count Ifs using multiple criteria sorted by date
    By Jamie_QHSE in forum Excel General
    Replies: 1
    Last Post: 12-18-2017, 09:22 PM
  3. VBA Help - Loop to check/count against rows using date & time criteria
    By bellpiero11 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-02-2017, 01:09 PM
  4. Replies: 4
    Last Post: 04-28-2017, 11:06 AM
  5. count the number of occurance based on time and date and specific criteria
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2014, 03:34 PM
  6. Replies: 5
    Last Post: 01-08-2013, 02:53 PM
  7. Replies: 0
    Last Post: 08-28-2005, 10:35 AM

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