# 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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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 ...???

Hi,

It is actual numbers formatted as dates.

Hi,

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

Hi,

It works! Thanks so much

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

Hi,

Thanks for the feedback ...

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

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

Thanks for your solutions ..

Replies
16
Views
682
Replies
1
Views
148
Replies
8
Views
269
Replies
5
Views
243
Replies
1
Views
77

1,206,761
Messages
6,074,780
Members
446,087
Latest member
PinkFloyd

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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