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

 

[ScottyDo]

Hi guys,

This is what I did to get it to work. In column P I wrote "=value(J2)" and did that down the whole column.
The result was a number and blanks came up as =.

I used this formula and it did the job....FINALLY. =COUNTIFS(Dump!H:H;A16;Dump!L:L;D9;Dump!P:P;">0";Dump!P:P;"<=15.02.2016")

I want to thank you guys for responding and helping as your input DID get me to think how to get this formula to recognize a number.

Cheers,
Scott