Countif with dates does not work

0
votes

I couldn't find a similar question that's why I asked a new one.

I have a worksheet with two columns - first column shows month and year in the following format "mon.year", for example "apr.2017" and the second one shows a number. The number must represent the count of some other dates filtered by specific criteria. I have those dates in another sheet - one column with name and one with a date. What I want to do is count the number of dates for example from june 2017 from the second sheet and place the number in the second column in the first sheet.

Here is how the first sheet looks like:

enter image description here

And this is how the second sheet with data looks like:

enter image description here

The result I'm looking for is this:

enter image description here

So, I tried the following code but I can't figure out why it doesn't work.

enter image description here

Any suggestions how to improve the formula?

EDIT: So, both the comments below were helpful for me but as the formula had to be simplified at most (my colleagues work with the excel file, not me) I chose to calculate the month and year in two hidden columns, then use the countif formula on them.

2
excelexcel-formula

2 Answers

2
votes

You will need to bracket the dates:

=COUNTIFS('1'$B$2:$B$26,">=" & EOMONTH(A2,-1)+1,'1'$B$2:$B$26, "<" & EOMONTH(A2,0)+1)
1
votes

For Excel, a date/time is just a formatted number, the number of days since a fixed epoch.

"="&MONTH(A2) is an expression which concatenates the string = with the result of the function MONTH(A2), which is 1. The result of the expression is =1, which is what COUNTIFS sees.

You're actually telling COUNTIFS to match the date/time whose serial number is 1 (December 31, 1999 at midnight). Similarly, the second criteria will match exactly on July 9, 1905 at midnight.

My suggestion for you is to compute the month and year of each date on separate columns, and use COUNTIFS on these columns.