Countifs not returning the correct value

Question

I have the following data that is all stored as text, with cell format at default general.

=ISTEXT(G4)
=TRUE
'in both spreadsheets.

Sample Data

Both workbooks similar. Different Date in A1, and different row entries here and there.

+---+-----------+-------------+-----+-----+------+-------------+--------------+----------+
|   |     A     |       B     |  C  |  D  |  E   |      F      |      G       |    H     |
+---+-----------+-------------+-----+-----+------+-------------+--------------+----------+
| 1 | MTOD List Exported as at Monday, 07 October 2019         |              |          |
| 2 |           |             |     |     |      |             |              |          |
| 3 | MTOD      | Title       | Dwg | Man | CDED | Issue Date  | Impl. Date   | Status   |
| 4 | 0101.0700 | Legend      |     |     |      | Apr 1, 1994 | Apr 1, 1994  | Active   |
| 5 | 0202.0310 | Roadway - A | 0   |     |      | Dec 1, 2005 | Dec 21, 2005 | History  |
| 6 | 0202.0310 | Roadway - A | 1   |     |      | Jan 1, 2007 | Mar 15, 2007 | History  |
| 7 | 0202.0310 | Roadway - A | 2   |     |      | May 1, 2009 | Jun 11, 2009 | Obsolete |
| 8 | 0202.0320 | Roadway - D | 0   |     |      | Jan 1, 2007 | Mar 15, 2007 | History  |
+---+-----------+-------------+-----+-----+------+-------------+--------------+----------+

When I perform a check for unique entries from the updated workbook, I enter the following formula in excel:

=COUNTIFS('[MTOD 115450 List.xml]Sheet1'!$A:$A,A4,'[MTOD 115450 List.xml]Sheet1'!$G:$G,"<>"&G4,'[MTOD 115450 List.xml]Sheet1'!$H:$H,H4)

Line 4 in the updated book is exactly the same as the sample data. Meaning that entry has not changed. What I am looking for is the same MTOD number (Col A), Different Imp. Date (Col G), Same Status (Col H)

Since line 4 is an exact match, I would expect the countifs to return 0, but it returns 1 instead. Left me confused. I broke the countifs into each of its ifs to see if I could catch something. and I got the following results:

• The MTOD value of 1 is as expected as its the only listing in the entire column.
• The Status value of 339 is as expected as there are that many active documents in the list
• The IMP of 1048576 is way off.

The value for IMP is the number of rows in a spreadsheet. when I apply the filter to that column, row 4 is actually the only row with that date. I could have understood a count of 1048576 -1. When I change the <> to = then I get the expected answer of 1.

In the sheet where I am doing the count, I used COUNTA(G:G) and got an answer of 1015.

Why is COUNTIFS giving the value it is for the IMP DATE?

What do I need to to make the full COUNTIFS formula evaluate to 0?

I suspect it my be converting the date string to an excel date as part of the process and it ends up trying to compare a string to an integer. I would buy that if it were not it returning the correct value of 1 when I switch the date to from <> to =.

This was all brought to my attention while trying to debug my excelformula which was returning the same results in 1 instead of zero:

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Range("A:A"), varElement.Value, _
                                                    Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "<>" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value, _
                                                    Existing.Worksheets("Sheet1").Columns(StatusCol.Column), "Active")

Is my logic flawed?

Update

Link to files

• 02-MTOD 200124 List.xml (Source)
• MTOD 115450 List.xml (Existing)

Update 20/02/12

I have revamped my program and it now converts the dates from strings to serial dates when the workbooks are opened. Now when I do the countifs in excel formula I get the right result of 0. When I run it through VBA I get the wrong result of 1. Any ideas on what is happening with VBA vs Excel formula?

one thing I noticed when checking the values of the countifs criterias in the watch window is that the date in the watch window is 94/04/01 and the date in the formula bar when I click on the cell displays 1994/04/01.

VBA Watch

Excel Date

The 0 in I5 is the result of the test COUNTIFS formula above it giving the expected result.

UPDATE II 20/02/12

so I did a little testing on just the date component. I can see the problem, but I do not know the work around currently. It appears when you concatenate the comparison operator to the date, it converts it to a string instead of leaving it as a date.

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value)

SearchCount will result in 1 which is correct as this is a unique date in that column.

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "=" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value)

SearchCount = 0

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "<>" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value)

SearchCount = 1048576

So how do I perform a COUNTIFS with a <> DATE? Can I change the DATE in VBA to its serial date and use that instead?

Answer 1

So the problem in the end is that Concatenating the operator to the date changes the date to a string. As a result the serial date stored in the column does not properly compare to the string. The solution is to grab the serial date which apparently is stored in .value2. As soon as the VBA formula changes to:

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "=" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value2)

or

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "<>" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value2)

you wind up getting the correct results provided the date in the column you are serching in are excel serial dates and not text dates.