count the number of times a substring is found within a date range in excel

Question

I have a spreadsheet that contains test data. column A has the test name and column B contains the test date. I want to count the number of times that the string Rerun is found within a certain date range.

For example

A                B
test1            11/2/2012
test2            11/7/2012
test1_Rerun_1    11/10/2012
test2_Rerun_1    11/16/2012

I am doing a weekly report so want to show how many tests had to be rerun in a particular week. so in the above example:

week ending 11/2/12 would return 0 (look for dates >10/26/12 and <=11/2/12 with substring "Rerun")

week ending 11/9/12 would return 0 (look for dates >11/2/12 and <= 11/9/12 with substring "Rerun")

week ending 11/16/12 would return 2 (look for dates >11/9/12 and <=11/16/12 with substring "Rerun")

Answer 1

Taken from the Microsoft documentation: Excel Formulas

COUNTIF will allow you to count the number of occurrences of text over a range. A quick skim shows that it will pick up substrings too.

Answer 2

For substrings within a date range you can use COUNTIFS (with an "S" on the end) with wildcards, e.g.

=COUNTIFS(A:A,"*Rerun*",B:B,">="&D2,B:B,"<="&D2+6)

where D2 contains the Start of week date

Note: COUNTIFS is available in Excel 2007 and later versions