COUNTIFS with Date Criteria Help

bjessee

New Member
Joined
Aug 20, 2019
Messages
2
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:

<tbody>
</tbody>
COUNTIFSCOUNTIFS "="COUNTIFS ">"SUMPRODUCTDate/Time Created
104305/17/2019 00:17:00
110008/17/2019 00:37:06
221108/17/2019 00:37:05
221108/17/2019 00:37:05

<tbody>
</tbody>

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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
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
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top