Need a formula that can count the number of times a name appears in a certain date range

Hi everyone! I am an intermediate/advanced Excel user, but I can't figure this one out. I assume I use a COUNTIF function, but I have tried many different formulas and nothing is working. The data in this spreadsheet is dynamic, so I can't use specific small cell ranges. I need to be able to find out how many times a name i H1-H1000 is listed in the date range (The J column) 06/01/16-06/30-16. Can anyone help?

A pivot table could do that for you, perhaps even with a helper column. If you don't wish to use this, there is a formula that could work:

=COUNTIFS(H1:H1000,"Ben Hoffman",J1:J1000,"<="&date(2016,6,30),J1:J1000,">="&date(2016,6,1))

Try

=COUNTIFS(H:H,X1,J:J,">=" & X2,J:J,"<=" & X3)

X1 =name
X2=Start Date
X3=End Date

Thanks for the quick replies! Unfortunately, I have already trie #1, and #2 didn't work either. I am seriously stumped-- there has to be a way to do this.

Post a sample file. Given your description, both formulae should work.

Spotted: The date field is Date +Time: you need to separate into a date-only field

e,g Z2=int(J2) formatted as date

or try

=sumproduct((h2:h1000)=X1)*(int(j2:j1000)>=x2)*(int(j2:j1000)<=x3))

To upload file, click "Go Advanced" then scroll down to "Manage Attachments"