# Count dates in colume that does not equal to certain year & month

#### balwy

##### New Member
Hi,

Does anyone know how to count dates in column that does not equal to certain year and month?

For eg: I want to calculate the number of dates that do not fall with Mar-2015 (excluding the blank fields).

I tried using the sumproduct function but it gives me error code "#VALUE!"

=SUMPRODUCT(--(TEXT(X5:X14,"yyyymmm")<>(--(TEXT(Z8,"yyyymmm")))))

Month : Mar-2015

 End Date 12-May-2015 14-Apr-2015 27-Feb-2015 24-Mar-2015 4-Jun-2015 31-Mar-2015

<tbody>
</tbody>

Thanks.

<colgroup><col></colgroup><tbody>
</tbody>

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### James006

##### Well-known Member
Hi,

The very first question you do have to ask yourself is the following :

In my database, am I using strings or actual numbers formatted as Dates ...???

#### balwy

##### New Member
Hi,

It is actual numbers formatted as dates.

#### James006

##### Well-known Member
Hi,

With your reference month in cell C1 ... you could test ... =SUMPRODUCT((MONTH(A2:A12)<>MONTH(C1))*(A2:A12<>""))

#### balwy

##### New Member

Hi,

It works! Thanks so much

But I probably need some time to understand how the logic works...

#### James006

##### Well-known Member
Hi,

Thanks for the feedback ...

##### MrExcel MVP
Including the year test...

=SUMPRODUCT(1-(TEXT(X5:X14,"yyyymmm")=Z8))

where Z8 = 'May-2015.

Or if Z8 = 1-May-2015 (a true date)...

=SUMPRODUCT(--(X5:X15-DAY(X5:X15)+1=Z8))

Or again

with Z8 = 1-May-2015...

=COUNTIFS(X5:X14,">="&Z8,Z5:X14,"<="&EOMONTH(Z8,0))

#### James006

##### Well-known Member

With only 5 months in 2015 ...missed the constraint of the year ...

Replies
6
Views
79
Replies
3
Views
169
Replies
2
Views
143
Replies
8
Views
115
Replies
1
Views
171

1,127,500
Messages
5,625,145
Members
416,075
Latest member
TechJosh

### 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