Countifs matching year and month

[pedexe90]

Hi everyone,

I don't understand what's wrong with my formula, I am trying to count the number of received dates each month within that year with the following function:

COUNTIFS(MONTH('(DL here) queryTool'!$B$2:$B$48095),"="&'Graph 1 - Monthy Registered'!A5,YEAR('(DL here) queryTool'!$B$2:$B$48095),"="&'Graph 1 - Monthy Registered'!$A$4)

I have made a sample and uploaded it on OneDrive if you would like to have a look.

Thanks

https://1drv.ms/x/s!AgQdYsSdg735f8VN4vTtXjQMW5c

 

[shaowu459]

I can't reach your file on Onedrive. However, I may told you where is wrong for your formula. The first parameter of COUNTIFS must be a range, for example A1:A100, it can't be an array calculated by other formula or function(except those returns a range reference). So you can't use month(range) as the first parameter of COUNTIFS.

 

[shaowu459]

may be the following formula will returns what you want

=SUMPRODUCT((MONTH('(DL here) queryTool'!$B$2:$B$48095)='Graph 1 - Monthy Registered'!A5)*(YEAR('(DL here) queryTool'!$B$2:$B$48095)='Graph 1 - Monthy Registered'!$A$4))

 

[shaowu459]

Simply, its' pattern is:

=sumproduct((list1=criteria1)*(list2=criteria2))

 

[pedexe90]

Simply, its' pattern is:

=sumproduct((list1=criteria1)*(list2=criteria2))

Thanks for the quick reply.

The function =SUMPRODUCT((MONTH('(DL here) queryTool'!$B$2:$B$48095)='Graph 1 - Monthy Registered'!A5)*(YEAR('(DL here) queryTool'!$B$2:$B$48095)='Graph 1 - Monthy Registered'!$A$4))

Returns zero values. May ask another question, whats the difference between --() when defining criteria in a sumproduct function and using *?

https://1drv.ms/x/s!AgQdYsSdg735f8VN4vTtXjQMW5c

 

[shaowu459]

The first question, please use Ctrl+shift+enter run the formula, not just hit Enter to finish.
The second question, --(),+0,-0,N() can change logical value TRUE and FALSE to 1 and 0, "*" works as well. For example, TRUE*TRUE=1, TRUE*FALSE=0.

 

[pedexe90]

So I just applied the formula as you instructed followed by a Ctrl+shift+enter, but it still returns zero.

=SUMPRODUCT((MONTH(A2:A6471)=C4)*(YEAR(A2:A6471)=C3))

Can you still not access https://1drv.ms/x/s!AgQdYsSdg735f8VN4vTtXjQMW5c

 

[shaowu459]

Sorry, I can't open the file. I can't ever open the link I created for my file on Onedrive, may be it because the server is in USA.

The form of formula =SUMPRODUCT((MONTH(A2:A6471)=C4)*(YEAR(A2:A6471)=C3)) is right. Can you send the attachment to me at shaowu459@163.com?

 

[steve the fish]

You can use countifs for this type of count. You would just count what was greater then or equal to the 1st of the month in question and less than the 1st of the next month. As an example based on the file:

=COUNTIFS(A:A,">="&("1"&C4&$C$3),A:A,"<"&EOMONTH(("1"&C4&$C$3),0)+1)

The "1"&C4 bit may need to be C4&"1" based on the locale.