I have two spreadsheets: (1) Contains an array of dates in this format: 22.08.2016 13:30 (2) Contains an array of ongoing month dates in this format: 08.2016
What I want to do is use "COUNTIF" to count all rows in spreadsheet (1) that match the month in spreadsheet (2).
My approach: =COUNTIF(MONTH('spreadsheet1'!H1:H); "=MONTH('spreadsheet2'!D3)") only returns "0".
Can you advice?
Try counta + filter:
=COUNT(IFERROR(FILTER(A:A,MONTH(A:A)=MONTH(B1)),""))
This formula may work too:
=ArrayFormula(SUM(if(MONTH(A:A)=MONTH(B1),1,0)))
Or even a bit harder, but this arrayformula will expand automatically, no need to drag down:
=QUERY(TRANSPOSE(QUERY(ArrayFormula(if(MONTH(A:A)=TRANSPOSE(MONTH(FILTER(B:B,B:B<>""))),1,0)),"select sum(Col"&JOIN("), sum(Col",ARRAYFORMULA(row(INDIRECT("A1:A"&COUNTA(B:B)))))&")",0)),"select Col2",0)
