Count(if(and(...

orbea_adam

Well-known Member
Joined
Nov 15, 2005
Messages
500
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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...
 
Upvote 0
Interstingly enough, =MONTH("June2008") returns 6. So it looks like something that shouldn't work, actually does.
 
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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