Oct 04 2017
03:13 PM
- last edited on
Jul 25 2018
10:11 AM
by
TechCommunityAP
Oct 04 2017
03:13 PM
- last edited on
Jul 25 2018
10:11 AM
by
TechCommunityAP
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.
Oct 04 2017 03:53 PM
Oct 05 2017 02:50 AM
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)
Nov 22 2021 01:26 AM
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.
How can I fix this. Thanks.
Nov 26 2021 06:20 AM
Did you try more exact ranges, like AX7:AX10000, etc.?