Countif using a date range

[fekka]

Hey everyone, I have a bit of a problem ... (fancy that)

I have the following formula

=COUNTIF(E$15:E$65536,"Pass")/COUNTIF($B$15:$B$65536,"**")

that counts up all of the "pass" records (rows) and gets me a % when looking at all of the records (rows), however I want to take this one step further and only count records (rows) that have been taken in the last 3 months (ie from today, but going back 3 months). Each record (row) already has a date field (column C)

I am running Excel 2003 on WinXP.

Can someone help please?
Thanks

 

[cstimart]

Here's a VBA function you can put in a Module, then in any cell type "=Date_Range()" and format as percentage.

Function Date_Range() As Single
Dim LR As Long
Dim i As Long
Dim x As Long
Dim y As Long
Dim d8 As Date
d8 = DateSerial(Year(Date), Month(Date) - 3, Day(Date))
y = Application.CountA(Range("B15:B65536"))

LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 15 To LR
    If Range("C" & i) >= d8 And Range("E" & i) = "Pass" Then
        x = x + 1
    End If
Next i

Date_Range = x / y
End Function

 

[fekka]

Thanks for that, but is there anyway of using a formula to get the same answer?

 

[cstimart]

Off the top of my head, I don't think so...unless you had Excel 2007.

 

[John Davis]

Thanks for that, but is there anyway of using a formula to get the same answer?

I'm not good with formulas, but maybe try something like this??

=SUMPRODUCT((E2:E10="PASS")*(C2:C10>TODAY()-90))/COUNTIF(B2:B10,"**")

 

[fekka]

Cheers John .... it works beautifully.

 

[fekka]

ok ... so that suits the purpose, however using the same formula wont work for the current month, as it uses the entire range ie. I have one entry for the current month that shows as a pass, out of 20 records, therefore the result should be 100%, however because it is dividing by the 20 records, it gives a 5% display ... any ideas?

This is what is causing the 5% result
=SUMPRODUCT(($B$37:$B$63362="PASS")*($C$37:$C$63362>TODAY()-31))/COUNTIF($B$37:$B$63362,"**")

Thanks

 

[T. Valko]

ok ... so that suits the purpose, however using the same formula wont work for the current month, as it uses the entire range ie. I have one entry for the current month that shows as a pass, out of 20 records, therefore the result should be 100%, however because it is dividing by the 20 records, it gives a 5% display ... any ideas?

This is what is causing the 5% result
=SUMPRODUCT(($B$37:$B$63362="PASS")*($C$37:$C$63362>TODAY()-31))/COUNTIF($B$37:$B$63362,"**")

Thanks

I think you'd only want to divide by the count of dates that are >TODAY()-31.

Maybe this:

=SUMPRODUCT(--($B$37:$B$63362="PASS"),--($C$37:$C$63362>TODAY()-31))/SUMPRODUCT(--($C$37:$C$63362>TODAY()-31))