Wierd Countifs issue

[s_woodbury]

So I'm having a weird countifs issue. I'm counting based on three variables but I'm getting a "0" result which isn't correct.

I narrowed the error down to one criteria because if I take it I get results.

Sheet Lead Data Upload contains data
Column K is populated with dates.
They are formatted as "date" type "*3/14/2012".
I want to count only if they are greater than Nov 30, 2015.

Formula:
=COUNTIFS('Lead Data Upload'!K:K,">11/30/2015",'Lead Data Upload'!E:E,P2,'Lead Data Upload'!D:D,B4)

I have tried to put "11/30/2015" in a cell and reference it that way. using ">"&$A$1 that didn't work either.
I changed the date format in K:K to 03/14/2012 - nada.

Any ideas?

 

[Joe4]

Are your values in column K entered as Dates or Text?
You can confirm with a simple check, like this (for a cell in K with a date in it):
=ISNUMBER(K4)
If it returns FALSE, your entry is Text, and not a valid Date.

Bear in mind that the issue could also be with your other two conditions, and not necessarily the date part.
Try starting out with one condition, and adding one at a time once you have each part working correctly.

 

[s_woodbury]

Thanks Joe, I did format the column to Date but when I use Isnumber() it returns false. I copy and pasted the column with values only, reformatted the column as dates and still get ISnumber()=false.......hmmmmm?

 

[Jonmo1]

Try copying a blank cell
Highlight the problematic column of dates
Right Click - Paste Special - Values - Add - OK

 

[Joe4]

Thanks Joe, I did format the column to Date but when I use Isnumber() it returns false. I copy and pasted the column with values only, reformatted the column as dates and still get ISnumber()=false.......hmmmmm?

You can try Jonmo1's suggestion or try using Text to Columns on that column, choosing the appropriate date option (MDY).

 

[s_woodbury]

OMG, thank you! That worked.

 

[Jonmo1]

You're welcome.