# COUNTIFS not working... arghh!

#### caj1980

##### Board Regular
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.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try

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

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!

So A1 is =NOW()-7

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 "

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)

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.

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

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 ?

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

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

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?

Replies
3
Views
127
Replies
3
Views
134
Replies
2
Views
86
Replies
3
Views
160
Replies
10
Views
178

1,196,515
Messages
6,015,661
Members
441,914
Latest member
VBAllTheThings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back