# Count(if(and(...

##### Well-known Member
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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Jonmo1

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

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

#### MrRajKumar

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

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

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

##### Well-known Member
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..

Replies
6
Views
1K
Replies
5
Views
226
Replies
3
Views
495
Replies
2
Views
1K
Replies
0
Views
228

1,190,860
Messages
5,983,269
Members
439,833
Latest member
CDaviess

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