Countifs not returning correct count

[ScottyDo]

Hi, I'm using:


Norwegian Win 8.1
English MS Office Professional 2016 with Norwegian keyboard


I'm trying to do a count of an item (in column H), that has a 'end-of-contract date' of 14.02.2017 (in column L), that has a 'warranty end date' of 15.02.2016 or earlier (in column J).


Not all items have a warranty end date and thus are blank in column J.


I've used this formula to find these items with blanks and it works just fine.
=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;"14.02.2017";Dump!J:J;"")


I've manually gone in and checked the returned count and it is correct.


But when I want to find an item with a warranty date less than 16.02.2016, it returns "0", when I know I have approx 2500 items with a warranty end date less than 16.02.2016.


=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;"14.02.2017";Dump!J:J;"<16.02.2016")


"Dump" is the sheet name where the data lies.


Any ideas why this doesn't work? Mind you the date format we use here is European: dd.mm.yyyy


Cheers and thx for any guidance,
Scott

 

[Marcelo Branco]

Maybe this

=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;DATE(2017,2,14);Dump!J:J;"<"&DATE(2016,2,16))

M.

 

[ScottyDo]

Hi...thx for your reply.
This did not work. It did not execute when I copied it into the cell and hit enter.
The parentheses become highlighted in red is all that happensed.

=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;DATE(2017,2,14);Dump!J:J;"<"&DATE(2016,2,16))

 

[Marcelo Branco]

Adjust/correct the argument separators

Try
=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;DATE(2017;2;4);Dump!J:J;"<"&DATE(2016;2;16))

M.

 

[ScottyDo]

Hi,

Thx...but it returns "0".

Scott

 

[Marcelo Branco]

Are you sure there is a row that satisfies both criteria L=DATE(2017;2;4) and J < DATE(2016;2;16)?<date(2016;2;16)?


Check if the values in columns J and L are real dates, not text.

Try
=ISNUMBER(J2)
and
=ISNUMBER(L2)

M.</date(2016;2;16)?

 

[ScottyDo]

Both return a 'False' response. Both are defined as "date" in the cell format menu.

 

[Marcelo Branco]

Maybe this can help
https://support.microsoft.com/en-us/kb/291047

See Method 4 Use Paste Special Command

M.

 

[ScottyDo]

OK, I've done this and there is no change.

I don't understand why this can handle 2 criteria using dates, but not when a third criteria is entered.
I surely appreciate your help thus far, but I think I'll have to just do this particular calculation manually.

Cheers,
Scott

 

[MARK858]

Your cells are not recognised as dates (that is what the FALSE shows). Try the text to columns method in the link Marcelo provided. There is nothing wrong with the formula.

Are you sure that your regional date separator is a full stop/period and not a forward/common slash(/)?