Is there a way to use VLOOKUP in excel to count certain values in a table, but only for a certain range of the VLOOKUP index?

Question

I have a large Excel file with dates as one of the variables. I am trying to sum a count of strings from one of the other variables, but only from a certain date range (i.e. count of "Yes" in column C for the most recent 10 days in column A). The date I need to start from is displayed on the main sheet (also where I need to display the count), with the data with the dates being on another sheet. Say whole table ranges from June 1-July 1 in column A, and column C is "yes" or "no".

I'm looking to VLOOKUP the date from the mainsheet to find where it begins in the dates sheet, and count the number of "yes" associated with that start date up until the current date.

I've tried using the INDEX function, making the array parameter begin from the date using VLOOKUP and end with the cell at the last date of the yes/no column (to incorporate all values needed from the date range), with no success.

=index(vlookup(B1,dates!A:A,1,false):D1000,5,countif(E:E,"Yes"))

I am getting a #REF! error, stating that the parameter 2 value should be between 0 and 1. I've only tried different variations of this function and VLOOKUP, and can't seem to get around getting an error.

The expected output should be the count of "yes" in column D from the start date specified to the current date in column A.

EDIT: 1

2nd Edit: The dates are pulled from a website and are not formatted as dates, but rather abnormal strings: Apr 27, 20194/27 Apr 28, 20194/28 Apr 30, 20194/30 May 1, 20195/1 May 2, 20195/2 May 3, 20195/3

Answer 1

Use COUNTIFS:

=COUNTIFS(E:E,"Yes",A:A,">="&B1,A:A,"<="&Today())

The Above works if your dates are true dates, if Strings Use:

=COUNTIFS(INDEX(E:E,MATCH(B1:A:A,0)):E1040000,"Yes")