Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer

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

share|improve this answer
    
does the trick! thanks. –  ckr Dec 20 '12 at 14:51
    
@ckr Can you accept the answer please –  barry houdini Dec 20 '12 at 15:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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