Using COUNT(IF(... to count number of months in years

Hi guys,

I have a large list of dates and need to count the occurrence of months in given years (so that July 2013 and July 2014 don't cause double ups for example).

The formula I entered
"=COUNT(IF(AND(YEAR(C2:C176)=2013,MONTH(C2:C176)=7),C2:C176,0))"
returns a value of 1 (incorrect). If I take out the AND function and use EITHER MONTH or YEAR the function returns the correct individual values as is needed.

Any thoughts?

Regards,
Abid

welcome to the forum, Abid. if you want to use that, it has to be an array formula:
=COUNT(IF(YEAR(C2:C176)=2013,IF(MONTH(C2:C176)=7,1)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

not advisable as this can cause your workbook to calculate slower. the alternatives are:
=SUMPRODUCT((YEAR(C2:C176)=2013)*(MONTH(C2:C176)=7))

or even better since you have excel 2007
=COUNTIFS(C:C,">=1jul2013",C:C,"<=31jul2013")

notice the i can use the whole column for the last formula. the other 2 would cause the workbook to be slower if you do

One way...

=SUMPRODUCT(--(TEXT(C2:C176,"mmm yyyy")="Jul 2013"))

when more than 1 member helps, i think it's good to try/acknowledge the member's effort too.

Question is, even though I entered mine as an array it still did not work

AND doesn't work really well with arrays here. in your formula YEAR(C2:C176)=2013, you can select that & press F9 to calculate. you notice you'll get many TRUEs & FALSEs. but if you select AND(YEAR(C2:C176)=2013,MONTH(C2:C176)=7) & press F9, it returns you just 1 single result. most likely, that will be FALSE. that is because ALL C2:C176 needs to be Year 2013 & Month 7 to return TRUE. so if it's TRUE, you want C2:C176. if it's FALSE, you want a 0. so it returns a 0. COUNT will count numbers in the specified range. 0 is a number. hence it returns 1.

Second follow up question, can you explain your second formula?

again, when you do logical tests like what you did:
YEAR(C2:C176)=2013
MONTH(C2:C176)=7
they return a bunch of TRUEs & FALSEs. Excel recognizes TRUE as 1 & FALSE as 0. so
TRUE x TRUE = 1
TRUE x FALSE = 0
FALSE x FALSE = 0

so only when the 2 conditions are met (2013 & 7), then you will get a 1. SUMPRODUCT merely helps you multiply all the TRUEs & FALSEs, and then sum them up. a small eg.
Data Range

	C
2	Jul-13
3	Jul-14
4	Jul-14
5	Jul-13
6	Jul-13

the above would be:
{TRUE;FALSE;FALSE;TRUE;TRUE}*{TRUE;TRUE;TRUE;TRUE;TRUE}
so that's:
{1;0;0;1;1}
add them up & you get 3.

do mark this thread as Solved

Originally Posted by benishiryo

when more than 1 member helps, i think it's good to try/acknowledge the member's effort too.


AND doesn't work really well with arrays here. in your formula YEAR(C2:C176)=2013, you can select that & press F9 to calculate. you notice you'll get many TRUEs & FALSEs. but if you select AND(YEAR(C2:C176)=2013,MONTH(C2:C176)=7) & press F9, it returns you just 1 single result. most likely, that will be FALSE. that is because ALL C2:C176 needs to be Year 2013 & Month 7 to return TRUE. so if it's TRUE, you want C2:C176. if it's FALSE, you want a 0. so it returns a 0. COUNT will count numbers in the specified range. 0 is a number. hence it returns 1.


again, when you do logical tests like what you did:
YEAR(C2:C176)=2013
MONTH(C2:C176)=7
they return a bunch of TRUEs & FALSEs. Excel recognizes TRUE as 1 & FALSE as 0. so
TRUE x TRUE = 1
TRUE x FALSE = 0
FALSE x FALSE = 0

so only when the 2 conditions are met (2013 & 7), then you will get a 1. SUMPRODUCT merely helps you multiply all the TRUEs & FALSEs, and then sum them up. a small eg.
Data Range

	C
2	Jul-13
3	Jul-14
4	Jul-14
5	Jul-13
6	Jul-13

the above would be:
{TRUE;FALSE;FALSE;TRUE;TRUE}*{TRUE;TRUE;TRUE;TRUE;TRUE}
so that's:
{1;0;0;1;1}
add them up & you get 3.

do mark this thread as Solved

Thanks again mate! Sorry got caught up with meetings.

EDIT: Repped and solved! Booyah!

You're welcome. Thanks for the feedback!