Countif with time interval

Question

My Google Sheets:

name   paid   time
john    y     2015/08/02 14:14
john    n     2015/08/02 14:13
john    n     2015/08/02 14:12
pat     n     2015/08/02 14:11
peter   n     2015/08/02 14:10
greg    n     2015/08/02 14:09
ricardo n     2015/08/02 14:05
barack  y     2015/08/02 14:02
john    n     2015/08/02 20:14

I need to count people who don't pay or don't pay within 10 minutes (if the person doesn't pay during 10 minutes so he hasn't paid). I tried with :

=SUMPRODUCT(--(A2:A9<>"")/COUNTIF(A2:A9,A2:A9&""))-COUNTIF(B2:B9,"y")

which is a good start but I don't have the time interval as parameter.

In this case it returns 4 but not 5 as I wish because John paid in an interval 10 minutes (between 14:12 and 14:14) but not at 20:14.

For instance, it starts at 2015/08/02 14:12 for John (the first NO -> n) then let's see what happen in the next 10 minutes.

If nothing, so we consider he didn't pay. Else "y", so he paid

And we continue anyway later to see what happen for john n 2015/08/02 20:14 (he didn't pay because nothing after 20:14)

So total of 5 guys and not 4.

Answer 1

Short answer

First get the most recent date for each n and y categories, then count those names which the most recent n date is newer than the y date.

Remark

The part regarding "don't pay within 10 minutes (if the person doesn't pay during 10 minutes so he hasn't paid)" was deliberately ignored as the OP doesn't include details about how the data is collected and neither about when and how the calculation result will be used.

Explanation

Source data

Considering the following as the source data

+----+---------+------+-------------------+
|    |    A    |  B   |         C         |
+----+---------+------+-------------------+
|  1 | name    | paid | time              |
|  2 | john    | y    | 8/2/2015 14:14:00 |
|  3 | john    | n    | 8/2/2015 14:13:00 |
|  4 | john    | n    | 8/2/2015 14:12:00 |
|  5 | pat     | n    | 8/2/2015 14:11:00 |
|  6 | peter   | n    | 8/2/2015 14:10:00 |
|  7 | greg    | n    | 8/2/2015 14:09:00 |
|  8 | ricardo | n    | 8/2/2015 14:05:00 |
|  9 | barack  | y    | 8/2/2015 14:02:00 |
| 10 | john    | n    | 8/2/2015 20:14:00 |
+----+---------+------+-------------------+

STEP 1

Add the following formula to cell E1

=QUERY(A:C,"select A,max(C) where A <>'' group by A pivot B",1)

Result

+---+---------+-------------------+-------------------+
|   |    E    |         F         |         G         |
+---+---------+-------------------+-------------------+
| 1 | name    | n                 | y                 |
| 2 | barack  |                   | 8/2/2015 14:02:00 |
| 3 | greg    | 8/2/2015 14:09:00 |                   |
| 4 | john    | 8/2/2015 20:14:00 | 8/2/2015 14:14:00 |
| 5 | pat     | 8/2/2015 14:11:00 |                   |
| 6 | peter   | 8/2/2015 14:10:00 |                   |
| 7 | ricardo | 8/2/2015 14:05:00 |                   |
+---+---------+-------------------+-------------------+

Explanation of step 1

The result is a table showing the most recent date for y and n categories.

STEP 2

Add the following formula to cell I2

=ArrayFormula(SUM(IF(F2:F>G2:G,1,0)))

Explanation of step 2

Count each name which n date is newer than the y date.

Result

+---+---+
|   | I |
+---+---+
| 1 |   |
+---+---+
| 2 | 5 |
+---+---+

Single formula alternative

=transpose(
    QUERY(
       QUERY(
          A:C,
          "select A,max(C) where A <>'' group by A pivot B",1),
       "select count(Col1) where Col2 > Col3",1)
  )

Result

+------------+---+
| count name | 5 |
+------------+---+