COUNTIFS not working... arghh!

[caj1980]

I am using the following COUNTIFS formula to count the number of rows who meet two conditions. The first condition is that the Status column equals "RESOLVED"' the second condition is that the Resolved Date column is >= (greater than or equal to) Cell A1. Cell A1 is autocalcualted to be today's date minus 7. The end result should tell me how many rows were marked Resolved in the last 7 days (in this example the result should be 2). But it always comes out to ZERO.

=NOW()-7 = 3-Jan-14
Resolved (Last 7d)=	=COUNTIFS(B5:B8,"RESOLVED",C5:C8,">=A1")
Ticket #	Status	Resolved Date
52146	OPEN
52148	RESOLVED	3-Jan-14
52159	RESOLVED	5-Jan-14
52180	RESOLVED	1-Jan-14

<tbody>
</tbody>

Thanks in advance for the help!
Chris J.

 

[Jonmo1]

Try

=COUNTIFS(B5:B8,"RESOLVED",C5:C8,">="&A1)

 

[caj1980]

It accepted that change but the result is still coming out as ZERO. I have double checked my arguments and everything is there and multiple rows match both arguments but they are not being counted.

Thanks!
~Frustrated!

 

[Jonmo1]

So A1 is =NOW()-7

Given your sample dataset
Either the Dates in C5:C8 are not really dates, just text strings that look like dates
Or None of the cells in B5:B8 = "RESOLVED" - Check the cells for leading or trailing spaces " RESOLVED" or "RESOLVED "

 

[Jonmo1]

Also, I'd recommend using TODAY() instead of NOW()

NOW() = today's date AND Time..
TODAY() = today's date only (technically considered at Midnight)
So at this moment, NOW() = Jan 10th @ 11:43AM (central time)

But if your cells in column C are just dates without the times (then they are technically at Midnight of each day, still a time value)
Then Jan 3 2014 @ Midnight will NOT be considered greater than NOW()-7
Because Now()-7 is Jan 3rd @ 11:43 AM (after midnight)

 

[caj1980]

yes, A1 is =NOW()-7
I was just showing the result for clarity.

I checked and all of the Fields in the Resolved Date column are formatted for date.
There are no leading or trailing spaces.

I think the problem has something to do with the dates. When I separate the COUNTIFS into separate COUNTIF statements the argument against the Status column works (result is 2). The argument against the resolved date column (>=A1) does not work (result is 0 when it should be 1). Is there a different formula function to use when dealing with dates? I've never seen that before; typically it is just a formatting issue.

 

[caj1980]

Changed the A1 formula to =TODAY()-7 but same symptoms.

 

[Jonmo1]

I checked and all of the Fields in the Resolved Date column are formatted for date..

It doesn't actually matter if the cells are 'formatted' as a date.
What matters is if they actually 'contain' dates.
It's possible (and happens very frequently) that the actual contents of the cell doesn't match the format of the cell.

Test with ISNUMBER

=ISNUMBER(C5) - repeat for all dates.

Are they True or False ?

 

[caj1980]

The =ISNUMBER comes back TRUE for all "dates".

I guess I don't know how to enter the contents as "dates" instead of "numbers"?

 

[Jonmo1]

Dates ARE just numbers incrimenting by 1 since jan 1 1900.
They just have a specific format applied to display them as a date..
1 = jan 1st 1900
2 = jan 2nd 1900

So this is getting wierd because they are indeed dates/numbers.
And your test with the seperate countif functions isolated the issue to the dates (that was my next suggegstion by the way).

next question is, are those dates in C5:C8 actually the dates they appear to be?

Format C5:C8 to custom: mmm dd yyyy
What does it show?