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

balwy

New Member
Joined
Feb 5, 2012
Messages
5
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 ...???
 
Upvote 0
Hi,

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

It works! Thanks so much :)

But I probably need some time to understand how the logic works...
 
Upvote 0
Hi,

Glad you solved your problem ...

Thanks for the feedback ...
 
Upvote 0
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))
 
Upvote 0
Hello Aladin,

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

Thanks for your solutions ..
 
Upvote 0

Forum statistics

Threads
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?

Disable AdBlock

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
Back
Top