Unable to use YEAR() and MONTH() inside COUNTIF()

Copper Contributor

I am trying to count and sum a column of numbers based on month and year.

 

In the most simplest example, I made a blank workbook with a list of dates and then tried to count them based on month and it just returns 0.

 

Column A:

 10/1/2017
10/1/2017
10/2/2017
10/3/2016
10/1/2016
9/1/2017
9/1/2016

 

=COUNTIF(A:A, "MONTH(A:A)=10")

...is returning 0.

 

 

4 Replies

Tim, bit more

 

What the COUNTIF do is compare one by one A1, A2, etc with criteria you set by second parameter. In your case that's some string, no one day (which is actually the integer number) is equal to any string. Thus result is zero.

 

And you can't apply any formula to first parameter, something like =COUNTIF(MONTH(A:A),10) returns an error. First parameter shall be a range.

 

If calculate months for any year usual alternative, as Bryant suggested, is using of SUMPRODUCT. Here you may use calculation on range

0	=COUNTIF(A:A,"MONTH(A:A)=10")
5	=SUMPRODUCT((MONTH(A:A)=10)*1)
5	{=SUM(IF(MONTH(A:A)=10,1))}
5	{=SUM(--(MONTH(A:A)=10))}
3	=COUNTIFS(A:A,"<="&EOMONTH(DATE(2017,10,1),0),A:A,">"&EOMONTH(DATE(2017,10,1),-1))
3	=SUMPRODUCT((MONTH(A:A)=10)*(YEAR(A:A)=2017))

Practically that's the equivalent of third formula, or in more compact form forth one, but they are to be used as array formulas.

 

However, if your criteria is the month for the concrete year (other words combination of the year and month) you may use COUNTIFS as above (or latest SUMPRODUCT which returns the same)

 

@Sergei Baklan 

 

I am trying to sum colum AX if column AN is equal to Month (Jan) and Year (2020) and column AL equal to certain installer name. I have tried =SUMIFS(AX7:AX, AL7:AL,B65,AN7:AN, ">="&DATE(YEAR(D61),MONTH(G613),1)) B65 is variable for installer name, D61 is for year 2020 and G613 is for intended month january but it's not working all throughout the table.

 

_Eve26_0-1637573144875.png

 

How can I fix this. Thanks.

 

@_Eve26 

Did you try more exact ranges, like AX7:AX10000, etc.?