1
Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this COUNTIF(A1:A20,"MONTH()=1"), but it does not work. The answer for above set is 3.
dma_k
Posted 2015-07-01T16:34:29.463
Reputation: 312
2
The following works in Excel and Google Spreadsheets. I cannot confirm for OO-Calc.
=SUMPRODUCT(1*(MONTH(A1:A20)=1))
Where A1:A20 is the list of dates.
This formula works by generating an array of 1s and 0s (1 for dates with month equal to 1, 0 for all others) and adding the contents of the array. This is the same as counting those that match the month criterion.
Excellll
Posted 2015-07-01T16:34:29.463
Reputation: 11 857
I have found a side effect on Google Spreadsheets. =SUMPRODUCT(1*(MONTH(A1:A20)=12)) counts also empty cells. – dma_k – 2016-02-01T12:39:12.140
1The forum provided a solution to the problem: =SUMPRODUCT((A1:A20<>"")*(MONTH(A1:A20)=12)). – dma_k – 2016-02-01T17:21:01.560
3 looks like the correct answer to me. Why do you think it is wrong? – DavidPostill – 2015-07-01T16:37:10.103
@DavidPostill: The formulae I gave does not produce the correct result. – dma_k – 2016-01-22T19:49:50.740