Results 1 to 2 of 2

Thread: COUNTIFS with Date Criteria Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question COUNTIFS with Date Criteria Help

    I have a report I pull from one system into Excel format. I process it with some queries in Access and export it out to Excel. At no point do I actually edit the data in the field, but it is getting passed back and forth. The issue does happen in the source document, so the passing back and forth does not appear to be the issue.

    My ask was to provide a report of tickets that that had new tickets on the same equipment. So I used COUNTIFS, but I found my numbers were off. I isolated the problem to the dates. I was using COUNTIFS([Date/Time Created], ">" & [@[Date/Time Created]]), but I found that in some cases, the row was counting itself. I tried it a few different ways, and this is what I got:
    COUNTIFS COUNTIFS "=" COUNTIFS ">" SUMPRODUCT Date/Time Created
    1 0 4 3 05/17/2019 00:17:00
    1 1 0 0 08/17/2019 00:37:06
    2 2 1 1 08/17/2019 00:37:05
    2 2 1 1 08/17/2019 00:37:05

    The formulas for the columns were:
    1. =COUNTIFS([Date/Time Created],[@[Date/Time Created]])
    2. =COUNTIFS([Date/Time Created],"=" &[@[Date/Time Created]])
    3. =COUNTIFS([Date/Time Created],">" &[@[Date/Time Created]])
    4. =SUMPRODUCT(--([Date/Time Created]>[@[Date/Time Created]]))


    In the first row, concatenating the operator seems to give a different result than expected, and I am not sure why. Any help would be greatly appreciated, as I have run out of ideas.

    I tried using COUNTIF. I tried duplicating it outside of a table with the same results. I thought it might be a case of fractional seconds, but then I got stranger results. If I used COUNTIFS with concatenated operators on MOD([@[Date/Time Created]], 1), it worked as expected, but if I used MOD([@[Date/Time Created]], 1)*24*60*60, it would fail for all rows.

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: COUNTIFS with Date Criteria Help

    Hi,

    Can you explain based on above example what your expected result is because I've tried to recreate your issue but I couldn't.
    ABCDE
    1COUNTIFSCOUNTIFS "="COUNTIFS ">"SUMPRODUCTDate-Time Created
    211335-17-19 0:17
    311008-17-19 0:37
    422118-17-19 0:37
    522118-17-19 0:37

    Sheet2



    Worksheet Formulas
    CellFormula
    A2=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
    B2=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
    C2=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
    D2=SUMPRODUCT(--(Table1[Date-Time Created]>Table1[@[Date-Time Created]]))
    A3=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
    B3=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
    C3=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
    D3=SUMPRODUCT(--(Table1[Date-Time Created]>Table1[@[Date-Time Created]]))
    A4=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
    B4=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
    C4=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
    D4=SUMPRODUCT(--(Table1[Date-Time Created]>Table1[@[Date-Time Created]]))
    A5=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
    B5=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
    C5=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
    D5=SUMPRODUCT(--(Table1[Date-Time Created]>Table1[@[Date-Time Created]]))



    So can you add a column to your table showing the expected result on that line if the formula works.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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