Count occurrence of date in a column returning "wrong data type"

Question

I'm trying to count the occurrence of a given month in a column of months. I've tried various formulas using both COUNTIF and SUMPRODUCT, but every method returns "wrong data type" or 0. I've even created a simplified sheet with the most basic example, and it's still returning the error.

Any help would be appreciated:

The formula I'm using to count, for example, the number of times a date in April occurs:

=SUMPRODUCT((A:A>=DATEVALUE("4/1/2018"))*(A:A<=DATEVALUE("4/30/2018")))

Returns wrong data type

=COUNTIFS(A:A,">=4/1/2018",A:A,"<=4/30/2018")

Returns 0

Once this is working properly, I then will need to expand the formula to only count the number of months in the column corresponding to text in another column. In the pasted graphic above, how many 'dog' entries for April.

Answer 1

Use COUNTIFS, but your date comparison criteria can be constructed with the operator within quotation marks, followed by the ampersand, then the date in question "built up" using the DATE function, e.g.

=COUNTIFS(A:A,">="&DATE(2018,4,1),A:A,"<="&DATE(2018,4,30),B:B,"Dog")

EDIT:

The problem may be related to a discrepancy between the formatted dates, e.g. "4/30/18", and the regional date/time settings on your computer.

It should be noted that the entire date and operator, within quotation marks, should work as long as the date format matches the regional date/time settings on your computer. For example, the following formula works for me.

=COUNTIFS(A:A,">=4/1/2018",A:A,"<=4/30/2018",B:B,"Dog")

In any case, using the DATE function to build up the date from the month, day, and year will circumvent this possible issue.