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>
 

Some videos you may like

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
Joined
Apr 4, 2009
Messages
3,680
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 ...???
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

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

balwy

New Member
Joined
Feb 5, 2012
Messages
5

ADVERTISEMENT

Hi,

It works! Thanks so much :)

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

Glad you solved your problem ...

Thanks for the feedback ...
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Apr 4, 2009
Messages
3,680
Hello Aladin,

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

Thanks for your solutions ..
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top