COUNTIFS with Date Criteria Help

[bjessee]

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:
[TABLE="width: 559"]
<tbody>[TR]
[TD="align: left"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 559"]
<tbody>[TR]
[TD="align: left"]COUNTIFS[/TD]
[TD="align: left"]COUNTIFS "="[/TD]
[TD="align: left"]COUNTIFS ">"[/TD]
[TD="align: left"]SUMPRODUCT[/TD]
[TD="align: left"]Date/Time Created[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]05/17/2019 00:17:00[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]08/17/2019 00:37:06[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]08/17/2019 00:37:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]08/17/2019 00:37:05[/TD]
[/TR]
</tbody>[/TABLE]

The formulas for the columns were:

1. =COUNTIFS([Date/Time Created],[@[Date/Time Created]])
2. =COUNTIFS([Date/Time Created],"=" &[@[Date/Time Created]])<strike></strike>
3. =COUNTIFS([Date/Time Created],">" &[@[Date/Time Created]])<strike></strike>
4. =SUMPRODUCT(--([Date/Time Created]>[@[Date/Time Created]]))<strike></strike>

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.

 

[jorismoerings]

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.

Book1
	A	B	C	D	E
1	COUNTIFS	COUNTIFS "="	COUNTIFS ">"	SUMPRODUCT	Date-Time Created
2	1	1	3	3	5-17-19 0:17
3	1	1	0	0	8-17-19 0:37
4	2	2	1	1	8-17-19 0:37
5	2	2	1	1	8-17-19 0:37
Sheet2

Cell Formulas
Range	Formula
A2	=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
A3	=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
A4	=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
A5	=COUNTIFS(Table1[Date-Time Created],Table1[@[Date-Time Created]])
B2	=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
B3	=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
B4	=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
B5	=COUNTIFS(Table1[Date-Time Created],"=" &Table1[@[Date-Time Created]])
C2	=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
C3	=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
C4	=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
C5	=COUNTIFS(Table1[Date-Time Created],">" &Table1[@[Date-Time Created]])
D2	=SUMPRODUCT(--(Table1[Date-Time Created]>Table1[@[Date-Time Created]]))
D3	=SUMPRODUCT(--(Table1[Date-Time Created]>Table1[@[Date-Time Created]]))
D4	=SUMPRODUCT(--(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.