1

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")

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.