Count(if(and(...

[orbea_adam]

I have the following formula confirmed with ctrl+shft+entr:

{=COUNT(IF(AND(MONTH(M4:M30)=MONTH(A39&C33),YEAR(M4:M30)=C33),,))}

m4:m30=list of dates
a39="June"
c33=2008

The formula results in 1, which is the count of false occurrences. What am I doing wrong causing count of false instead of true. The answer should be 6.
Adam

 

[Jonmo1]

Let me see if I understand right...

You're constructing a Date from A39 and C33 - to equal June 1st 2008
And you want to count how many dates in M4:M30 are of the Same Month And Year of that Date, Right?

the problem is is that A39&C33 is not a valid excel Date, it's a text string. June2008. You need to convert that to a valid excel date...

Try this (no need for CTRL + SHIFT + ENTER)

=SUMPRODUCT(--(TEXT(M4:M30,"mmyyyy")=TEXT(A39&C33,"mmyyyy")))

Hope This helps...

 

[Seti]

Interstingly enough, =MONTH("June2008") returns 6. So it looks like something that shouldn't work, actually does.

 

[MrRajKumar]

Try this formuls

=<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_1596964 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>SUM(MONTH(DATEVALUE("01"&C39&"-"&C33)),-SUM(MONTH(M4:M30))+1)</TD></TR></TBODY></TABLE>


Hopes this works.

 

[Jonmo1]

You're right Seti...

It was actually the AND throwing it off...

To make it work as an array formula (CTRL + SHIFT + ENTER)
=COUNT(IF(MONTH(M4:M30)=MONTH(A39&C33),IF(YEAR(M4:M30)=C33,M4:M30)))

But, the Sumproduct (without CSE) is more efficient..

 

[lenze]

Why not just create a Pivot Table grouped by Months and Years and you can see all months (by year) at once? Probably take 10-15 seconds to construct.

lenze

 

[orbea_adam]

jonmo1, Both your solutions work, as you know. But, you're right...the sumproduct is my favorite, so **** simple! Thanks, Adam

You're right Seti...

It was actually the AND throwing it off...

To make it work as an array formula (CTRL + SHIFT + ENTER)
=COUNT(IF(MONTH(M4:M30)=MONTH(A39&C33),IF(YEAR(M4:M30)=C33,M4:M30)))

But, the Sumproduct (without CSE) is more efficient..