Countifs dynamic date criteria

0
votes

In a =COUNTIFS formula which I'm using, I am looking for data (specifically the number of granted patents) before and after a date. This date is listed in separate column and the last criteria in my COUNTIFS formula refers to this cell. However if I slide this formula downwards or sideways for my sample, the last criteria is not dynamic and keeps referring to the same cell (in the example: N2). How do I alter formula so that I can drag it downwards and sideways?

The current formula:

=COUNTIFS('1972-06-06 - 1988-01-12'!$E$2:$E$366963,'MA Acquiror Permno Cusip'!E2,'1972-06-06 - 1988-01-12'!$C$2:$C$366963,"<=N2")

I have tried a variant where the last criteria looks like:

"<=" & N2

yet this returns no hits, the previous formula does return hits but is not dynamic.

Thanks in advance for any help.

1
exceldynamicexcel-2010criteria
Your second attempt ["<=" & N2] is correct. Make sure both the column of data and the comparison cell are date format and not text. - Matt
The fact that your first formula does return hits is cause for concern. "<=N2" would be looking for text strings less than "N2". - Matt
@pnuts to answer your question yes - user3581665
@Matt Just to clarify the example I provided is meant to find values before N2, so that it does return values is correct. I use the exact same formula to find pattents after that date but use ">N2" - user3581665

1 Answers

1
votes

Just had this exact same problem and it took me an hour to figure out.

Apparently you need a space between the operator ("<") and the date.

"<= " & N2

^Should work. Notice the space after the equals sign.